I am new to SSIS, before this I worked with Informatica for 6 years. I am trying to do something very similar in SSIS which I could do in Informatica very seamlessly. I have say 100 rows from a Source table, and the rows are created in such a way, as follows Row1: A, B, C , D Row2: , Detail: info Row3: D, E, F , D Row4: , Detail: info . . … All I wanted to do is to create 4 variables and assign the 4 values to it (A, B, C and D), then when I see the second row and I don’t find A, B, C, or D columns I will pass in the variables values to it. The I read the 3 row and replace the values in the variables from A,B, C, D to D, E,F,D and so on and so forth. My final table will look something like this Row1: A, B, C , D Row2: A, B, C , D, Detail: info Row3: D, E, F , D Row4: D, E, F , D, Detail: info . . … I know in Informatica, I can do this easily with mapping parameters, but in SSIS its very challenging, since the user variable can only be used in SQL execute task, I dont want to ahve any SQL overwrite on my process. Can you please help?

What kind of columns are A, B, C, and D? What is "blank"? In SSIS, you're likely to have to use a Script component to get this done. A synchronous script should do, where you just "remember" the values you saw in the previous row, and "apply" them to the current row if those columns are blank. In SSIS, the data flow is "row centric" - looking back a row or forward a row isn't what it's main purpose is, but you can get it done with scripts pretty easily. Talk to me now on

Thanks Todd for your answers, The columns A, B, C and D are standard string columns (char (1)). All I am trying to get out of this is, I have some files (like 1000s of them) that has similar format / row&column structure, where row1 is structured properly (header information) and the detail information are followed by successive rows with a tab space in them. (since I know SSIS is a "row cenric" as Informatica is.. ) I wanted to use derived columns transformation & use my user variables in the below fashion. My logic is, if you find column 1 as char(1) then place the value in Variable, A = V1, B= V2, C= V3, D=V4 when SSIS reads the second row, and it finds a blank in the column 1 then replace column 1 with V1 (which is A), col2 with V2 (B), col3 with V3 (C) ,col4 with V4(D) and "Newcolumn5" with "entire values in the row2" By this, I will have a table full of header columns and detail columns With the reference to the header row (based on the fact they are its successor line) In this way I dont want to have any SQL scripting involved. Hope this helps

As Todd mentioned, you can do this with a Script Component (as Source or transformation) in SSIS. I see no other way to achieve your requirements

Is it necessary that you use variables here? In the source table you might be having some way to identify that Row1 is a header for Row2 - other than the columns A,B,C,D If so, you could use merge join in a DFT, join Source.R1 on Source.R2 (could be source1.ID = source2.ID+1), and place an oledb command to update the target table using columns from Source.R1

Hi, I think, using a Script Component is the best idea to achieve your functionality. You can have 4 more variables on your solution, and then, set their values using a Script component inside the ForEach loop as: Say, your mapping variables are M1,M2,M3,M4 and the 4 new variables are V1,V2,V3,V4 You can use a script task to Set the Value of V1 = M1(if M1 is not null).... And then insert V1,V2,V3,V4 into the SQL Server Tables. This way, if your Mapped variables receive a NULL or a blank, it wont overwrite the Previous row values stored in V1,V2... Hope, this helps you...Ashu_Blueray

Yes. You can do this with a Script. But SSIS variables won't be used at all, and in order for this to work, all of the files need to have exactly the same metadata - not "similar". SSIS is quite strict about metadata. You would be able to use a Foreach Loop Container with a File Enumerator to loop over files. Inside the Loop, you'd place a Data Flow Task. Inside that Data Flow Task, you'd have a Flat File Source. The Flat File Source would have a Flat File Connection Manager associated with it that would define the metadata for the columns in the file. The Flat File Connection Manager's ConnectionString property would have to be set via a Property Expression. The expression used in the ConnectionString property would reference the filename variable you used in the Foreach Loop. Then you would place a Script component in the data flow after the Flat File Source, and select the "transformation" type. Inside the script editor, you could choose either VB.Net or C#, whatever you're most comfortable with. You would select all of the columns as ReadWrite, then edit the script code. In the script code editor, you would define one .Net variable at the top of the class per column, with default values. In the Input0_ProcessInputRow method, you would examine the properties of the Row object - the column values. Each column will have two properties on that object, one named the same as the column (less illegal characters), and one named the column name plus "_IsNull". If the "IsNull" property is true, then the column contains a NULL value. (If you attempted to examine the contents of the column property itself, you would raise an error.) You would code the script to examine each column, and if it was null, assign that column your variable value. If it isn't null, you'd assign the row value to the variable (in preparation for the next row). Now you're done with the script. You'd then add some kind of Destination - I don't think you mentioned where you were sending the data to. (Oh - here I misinterpreted, I see you're loading from tables, not files. Oops. I won't rewrite the top part - it gives you a general idea. You'd use an Execute SQL Task to retrieve a rowset of tables, and use the Foreach ADO Enumerator to enumerate over the result, much the same as the file enumerator. The data flow source would be an OLE DB Source, and you could use property expressions to set the SQL statement.) Talk to me now on

Hi Varsha, Thanks for your answer, and sorry for the delay (for some reason my Alert is not working for this tag) Anyways, Since I am a informatica developer I have worked with variables a lot in Informatica and its "row centric" so its easy to manipuate, transform and change the values of certain columns based on every single row that is passing by. The source is a flat file, and I am expecting thousands of em.

Thanks Ashu, but I was seriously thinking to avoid any "scripting" stuff. Reason being, I always wanted it to be proper ETL and no coding involved in them. If any other person look at the SSIS package, all he has to know is what are the transformation and how the lookup or expression or derived column worked. At last, if the record is getting inserted, or updated in the target. Inserting with the Script means, I have to learn or make sure my scripts are in standards and the new person has knowledge in scripting stuff.

Thanks Todd, for your detail explanation, will try to see if that work and does the magic. :)