Loading Flat File into SQL Server using SSIS

Question

New to SSIS and am trying to import a flat file into my DB. There are 6 different rows on the flat file that I need to combine into one row in the database, each of these rows contain a different price for one symbol. For example below:

IGBGK  21 w  47 
IGBGK  21 u  2.9150  
IGBGK  21 h  2.9300    
IGBGK  21 l  2.9050   
IGBGK  22 h  2.9300      
IGBGK  22 l  2.8800

So each of these are in a different rows on the flat file but will become one row in different columns for symbol IGBGK. I can transform the data to place each number into its own column but can not get them to combine into one row.

Any help on the direction I need to go with this is greatly appreciated.

End product should look like:

Symbol | col 1 | col 2 | col 3 | col 4 | col 5 | col 6
-------+-------+-------+-------+-------+-------+-------    
IGBGK  |  47   | 2.915 | 29.30 | 2.905 | 2.930 | 2.880

I suggest you insert the flat file into a temp table (without any transforamtion) and then use the Execute SQL task to get the desired outcome. You can use PIVOT for that. — DenStudent

LONG LONG · Accepted Answer · 2016-11-02T18:44:15

1.Name a variable with whatever name you want with system object type

2.Use execute sql task

Query for you table:

WIth ABC as (Select * From table --which give you the original result ) Select * From ABC PIVOT (Count(**4th Column Name**) for **1st Column Name** IN ([col 1],[col 2],[col 3],[col 4],[col 5],[col 6]))

4.copy all the complete query into that task and specify the result Set to Full result

5.Switch to Result Set page, choose the variable you create, and set the result name to 0

6.Now every time you run the package the variable will be assigned as the complete result table as shown in your desired format above.

7.And specify another 7 variables corresponding to each column, "symbol, [col 1]...", should be string data type for each variable

Use another execute sql task, specify Variable in SQL Source Type, then go to the Parameter Mapping page, choose that System Object variable, set Name to 0, after that go to Result set page, choose all those seven parameters one by one, and change the parameter name to 0,1,2,3,4,5,6
From now on every time you run the package, each variable would be assigned each value, if you want to load them into target table, here comes the last step
Use another Execute SQL Task, using query like this:

Insert into table select ?,?,?,?,?,?,?

go to the Parameter Mapping page, choose all those seven variables and change name to 0,1,2,3,4,5,6 for each one by one to map the ?

There could be some small issue you need to figure by yourself, like the data type, but the logic is almost like this.

Hope this helps!

Loading Flat File into SQL Server using SSIS

1 Answers