SSIS import data

Question

SSIS import data

sqlfriend

SSC Guru

Points: 52781
More actions
December 8, 2011 at 6:46 pm

#253269

I have a ssis pacakge, I would like to import two flat source files into one SQL table , I will call it TargetTable.
File 1 has an age column, I want to filter out age>18,
So here is the basic steps I plan to do it.
1. Create two temp tables:
2. import file 1 into a temperatory table called Temp_table1, and import from file 2 into a temporary table called temp_table 2.
3. Then insert into my targetTable select temp1.* and temp2.* on temp1.id= Temp2.Id where temp1.age>18
Drop temp_table1 and temp_table2
Is this the right way of doing it, or any other suggestions?
When I say temp table, I really create a regular table with the name called something like Temp_, it is not #temptable in temp db.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

JonFox SSCrazy Points: 2157 More actions · Answer 1

sqlfriends (12/8/2011)
I have a ssis pacakge, I would like to import two flat source files into one SQL table , I will call it TargetTable.
File 1 has an age column, I want to filter out age>18,
So here is the basic steps I plan to do it.
1. Create two temp tables:
2. import file 1 into a temperatory table called Temp_table1, and import from file 2 into a temporary table called temp_table 2.
3. Then insert into my targetTable select temp1.* and temp2.* on temp1.id= Temp2.Id where temp1.age>18
Drop temp_table1 and temp_table2
Is this the right way of doing it, or any other suggestions?
When I say temp table, I really create a regular table with the name called something like Temp_, it is not #temptable in temp db.
Thanks

You can also keep it all in memory (no temp tables) by including the data sources for both flat files in a single data flow, adding a conditional split transform to exclude the records you don't want, sorting both paths and then doing a merge join before inserting into targetTable.

Flat File 1 Flat File 2

| |

| Split (on age)

| |

Sort Sort

| |

------Merge Join----------------

|

targetTable

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thank you, good to learn another option.

The only thing I am concerned is the records we have are about 400000.

I hear sort really affect performance, for this number of records, does it matter?

JonFox SSCrazy Points: 2157 More actions · Answer 3

sqlfriends (12/8/2011)
I hear sort really affect performance, for this number of records, does it matter?

Honestly, I haven't worked with SSIS enough at this point to be able to say from experience. I suspect it will be fine as long as the machine you're running this on has enough RAM to keep all 400,000 records in memory; if data starts to get swapped to disk due to insufficient memory, then performance will be terrible. I would suggest just trying it out and seeing what works in your environment.