Hey, I have a file, which looks like this: --Records Start Id = 1 Name = John INum = 2341 ### ### 1,20120823,141213,0001 1,20120824,141234,1234 ### ### --Records End I need to load this file into a sql table with Id,Name,INum,Date(Value=20120823),Snum(value=141213) How can i achieve this?

derived column Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

How will that be useful here?

does you source file has just one column? If that is the case then you can use script component destination, since you have flags indicating start and end of records. In the script component you save in variables the different fields until it reads the end of record, when it does you add a new record to the destination.

Use a Script Component as a Data Source. You can read the input file line-by-line and parse the elements you want from each line. Here is one of the better posts on how to do this: http://microsoft-ssis.blogspot.com/2011/02/script-component-as-source.html (Courtesy of SSIS Joost, a frequent contributor here). If you search the web for "Script Component as a Source," you'll find many more.

I'll be mapping multiple columns from the source to my destination table.. ! Isn't this possible without "Script Component Task" ?

I was trying to avoid "Script Component task". So, i was thinking more on the lines of Conditional Splits and Derived columns. Any suggestions?

You could probably accomplish the same thing with Derived Columns and/or Conditional Splits, but the best place to do it is in a Script Transformation.

I don't know much about .Net or C# coding, so I was trying to avoid it. Can you direct me, on how to use derived column and conditional splits for my problem ?!

You're going to have to use the string functions available in SSIS expressions (SUBSTRING, FINDSTRING, etc.) to parse each line to pick out the data you need. Something like this: SUBSTRING(@[User::StringVar], FINDSTRING(@[User::StringVar], "=", 1) + 2, LEN(@[User::StringVar]) - FINDSTRING(@[User::StringVar], "=", 1) + 2)

I don't know, how to use Script Transformation. Is there anything that I can add to my current logic (not using scripts), to get my results? And, you meant, after my 2 derived column and their data conversions, I should have a "Union All" and then my destination ?

I don't know, how to use Script Transformation. Is there anything that I can add to my current logic (not using scripts), to get my results? And, you meant, after my 2 derived column and their data conversions, I should have a "Union All" and then my destination ? Well then, it's time to learn. I've written a simple tutorial for you to work with your particular problem. Here's what you will be building: I've simplified the data this package will use, but it matches the format of your data. You will need to add a column to act as PersonID does here when you do this with your data. Using your text editor create a text file that matches mine. Unless you know how to enter a <Tab> character, use commas as your delimiter. The data looks like this: I would recommend following these instructions exactly, then creating a new package to work with your data where you can apply what you learn here. Create a new package. Name it whatever you like.Add a Flat File Connection Manager to the package. Name it Incoming Flat File. Leave the Text qualifier set to <none>, and Header row delimiter set to {CR}{LF}. Check the Column names in the first data row check box.Click on the Columns tab and ensure the delimiter is set correctly and the headers are correct. You should see three columns: PersonID, RecordID and KeyValuePair, and 12 rows of data.Click on the Advanced tab. Set the DataType for PersonID and RecordID to four-byte signed integer. Set the DataType for KeyValuePair to string and set OutputColumnWidth to 8.Click okay to save the Connection Manager settings.Add a Data Flow Task to the control flow design surface, and switch to the Data Flow Task design surface.Add a Flat File Data Flow Source to the Data Flow. Set it's Connection Manager to Incoming Flat File and ensure all three columns are selected for inclusion. Set the name of the Data Flow Source to FF_SRC - Incoming Flat File.Add a Conditional Split Transformation to the Data Flow and attach it to FF_SRC - Incoming Flat File. Set the name of the Conditional Split Transformation to CSPL - Split Types 1-3.Edit the properties of CSPL - Split Types 1-3. Create an output named Type 1-3. Set its Condition to: RecordID == 1 || RecordID == 2 || RecordID == 3 Set the Default output name to All Other Types.Add a Derived Column Transformation to the Data Flow. Attach it to the Type 1-3 Output of CSPL - Split Types 1-3. Set the name of the Derived Column Transformation to DER - Parse Value.Edit the properties of DER - Parse Value. Set the Derived Column Name to Value. Set the Expression to: SUBSTRING(KeyValuePair,FINDSTRING(KeyValuePair,"=",1)+2,LEN(KeyValuePair)-FINDSTRING(KeyValuePair,"=",1)+2) Add another Derived Column Transformation to the Data Flow. Attach it to the All Other Types Output of CSPL - Split Types 1-3. Set the name of the Derived Column Transformation to DER - Add Value.Add another Derived Column Transformation to the Data Flow. Attach it to the All Other Types Output of CSPL - Split Types 1-3. Set the name of the Derived Column Transformation to DER - Add Value.Edit the properties of DER - Add Value. Set the Derived Column Name to Value. Set the Expression to: KeyValuePair Add a Union All Tranformation to the Data Flow. Set the name of the Union All Transformation to ALL - Union All. Attach DER - Parse Value and DER - Add Value's Outputs to the Union All Transformation. Edit the properties of ALL - Union All. Right-click on the KeyValuePair line and delete it. It is no longer needed in the Data Flow. Add a Sort Transformation to the Data Flow. Set the name of the Sort Transformation to SRT - Sort. Edit the properties of SRT - Sort. Set the Input Column to PersonID. Click Pass Through for RecordID and Value. The Data Flow MUST be sorted for this technique to work. Add a Script Transformation to the Data Flow. Set the name of the Script Transformation to SCR - Custom Pivot. Edit the properties of SCR - Custom Pivot. Set the ScriptLanguage property to Microsoft Visual C#. Click on the Input Columns tab and select all three columns, RecordID, PersonID and Value. Click on the Inputs and Outputs tab. Click on Output 0. Set the SynchronousInputID property to None. This tells the Script Transformation that we will be adding rows to the Data Flow instead of just passing them through. Expand the Output 0 tree. Click on the Output Columns folder under Output 0. Click Add Column four times. This will add the following Output Columns: Column 3, Column 2, Column 1 and Column. Click on Column 3. Set the Name property to ID, and the DataType property to four-byte signed integer [DT_I4]. Click on Column 2. Set the Name property to Name and the DataType property to string [DT_STR], and the Length property to 8. Click on Column 1. Set the Name property to INum and the DataType property to four-byte signed integer [DT_I4]. Finally, click on Column. Set the Name property to Plays and the DataType property to string [DT_STR] and the Length property to 8. Click on the Script tab, and then on the Edit Script Button. Click Control-A to select the code in the Editor and press the Delete key. Add the code at the end of this post to the Editor.Click on Build in the Editor toolbar, then Build <whatever string is there>. If there are any errors you will see them now. If not, close the Editor. Click OK to close and save SCR - Custom Pivot. Direct the Output of SCR - Custom Pivot to what Data Source you like or add more Transformations to process the data further. I'm using a Trash Destination here. If you run your package at this point, you should see the same number of rows as mine. Code for SCR - Custom Pivot: using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { int _prevPersonID = 0; public override void Input0_ProcessInputRow(Input0Buffer Row) { if (_prevPersonID != Row.PersonID) { // this is a new person, add a new row. Output0Buffer.AddRow(); } // determine what type of row we are dealing with. switch (Row.RecordID) { case 1: Output0Buffer.Id = int.Parse(Row.Value); break; case 2: Output0Buffer.Name = Row.Value; break; case 3: Output0Buffer.INum = int.Parse(Row.Value); break; case 4: Output0Buffer.Plays = Row.Value; break; } // save the person id for comparison to the next row. _prevPersonID = Row.PersonID; return; } } I hope this helps. D.

I was thinking along these lines, let me know, if there's something wrong with this approach. Long story short, in the 1st step, I added a rowNumber to each file. So, the result will look like this: --Records Start (RowId column added) 1 Id = 1 2 Name = John 3 INum = 2341 4 ### 5 ### 6 1,20120823,141213,0001 7 1,20120824,141234,1234 8 ### 9 ### 10 --Records End After that I included a "Conditional Split Transformation", that has 2 cases, where RowId==1 and RowId ==4, for each of these cases I addded a "derived column" (RowId==1 for my Id column and RowId==4 for my Name column), where I do, my substring and other functions to change the data, I then connect each of these to 2 OLEDB destinations (which load in to the same table). This approach works. But , the problem is, for Case 1 data gets loaded in the 1st record, where as for Case 2, data gets loaded in to a new record. I WANT the Case 2 data to get loaded in the same record because its a different column I am loading to. For e.g. Data looks like this right now: Id Name Num 1 NULL NULL NULL ARKINSON NULL I want "ARKINSON" in the 1st record with Id=1 Let me know, if I wasn't very clear.

That's a pretty good approach except for the two data sources loading the same table. Run the output from both Derived Column transformations to the Union All transformation, then direct the output to to your data source. Your data will now look like this: RecordID Value 1 1 2 John 3 2341 4 ### 5 ### 6 1,20120823,141213,0001 7 1,20120824,141234,1234 8 ### 9 ### 10 --Records End Essentially, you are trying to PIVOT the data into rows, but you can't use a PIVOT transform on two columns. You're going to have to use a Script Transformation to do this, and to do that, each group of ten rows will need a common, unique identifier.