Can someone assit me with inserting a new row based on column values in an SSIS package(2005)? I'm currently reading from a flat file source and inserting into a SQL table. I'm also using a derived column to split the text file information character length. My problem is that I'm not sure how to insert a new row based off of column values during this process. If a certain value is contained in the output from ther derived column, I will need to insert a new row containing values from one column. For example: If the output from my derived column is the following: First Name Last Name No Data John Smith 4 aaakkkllmmmkkooo If the "No" column is equal to 4, I would have to create a new row containing substring(Data,1,2) + substring(Data,3,3) etc.... Any Suggestions?

Hi Use a conditional split to redirect the rows where [No] == 4. After that use a derived columns transformation to derive the input values (the substrings). the output from that can be directed to a destination HTHCraig Bryden - Please mark correct answers

So there would be a conditional split between two derived column transformations? I'm using a derived transformation to create the first table. If the columns from that table contain a certain value, I will have to insert new rows into the same table with the column substrings.

Also consider using lookups. The component to use is the Lookup Transformation. Example: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range May be you can adapt this - http://jahaines.blogspot.com/2009/09/ssis-only-inserting-rows-that-do-not.htmlArthur My Blog

that sounds about right then, without knowing all the details.Craig Bryden - Please mark correct answers

I guess I'm still trying to figure out how to insert a new row from the derived transformation expression. Can this be done from the transformation expression? if "No" == 4 ? insert new row based on substrings If not, what is the proper way to do this in SSIS?

To clarify: Transform your rowset to the point you've got it, which I understand you're ready to insert into "table A". Place a Multicast in the flow at this point, and send one flow to your existing destination that inserts into "table A". Place a Conditional Split in the flow, connecting it to the Multicast's output. You now have two flows coming out of the Multicast with the same set of rows. Use the Conditional Split as a "filter" component, with that column condition. Use a Derived Column after the Conditional Split to transform your row. Place a second Destination after the Derived Column (There are other ways to get this done - but that ought to work fine.) Talk to me now on

Thanks, this appears to be what I was looking for. I will test and see if it works for me.