How to insert a row into a dataset via SSIS

Question

I'm trying to create an SSIS package that takes data from an XML data source and for each row inserts another row with some preset values. Any ideas? I'm thinking I could use a DataReader source to generate the preset values by doing the following:

SELECT 'foo' as 'attribute1', 'bar' as 'attribute2'

The question is, how would I insert one row of this type for every row in the XML data source?

Answer 1

I've never tried it, but it looks like you might be able to use a Derived Column transformation to do it: set the expression for attribute1 to "foo" and the expression for attribute2 to "bar".

You'd then transform the original data source, then only use the derived columns in your destination. If you still need the original source, you can Multicast it to create a duplicate.

At least I think this will work, based on the documentation. YMMV.

Answer 2

I'm not sure if I understand the question... My assumption is that you have n number of records coming into SSIS from your data source, and you want your output to have n * 2 records.

In order to do this, you can do the following:

• multicast to create multiple copies of your input data
• derived column transforms to set the "preset" values on the copies
• sort
• merge

Am I on the right track w/ what you're trying to accomplish?

Answer 3

I would probably switch to using a Script Task and place your logic in there. You may still be able leverage the File Reading and other objects in SSIS to save some code.