Bulk insert task SSIS question

Question

Bulk insert task SSIS question

Guras

SSCertifiable

Points: 6518
More actions
March 18, 2015 at 10:33 am

#315848

Can we bulk insert only the desired column from a flat file to a table?
I am using SSIS to bulk insert from a file with more than 200 columns. I am trying to find a way I can bulk insert them to multiples table through SSIS.
The one way I can think is pre map the columns from the file to the destination tables. Build numerous Bulk Insert tasks to achieve that.
But not sure if SSIS will let me do that.
Any thoughts on any alternative ways with SSIS?

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

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

CKX SSCrazy Points: 2519 More actions · Answer 1

Guras (3/18/2015)
Can we bulk insert only the desired column from a flat file to a table?
I am using SSIS to bulk insert from a file with more than 200 columns. I am trying to find a way I can bulk insert them to multiples table through SSIS.
The one way I can think is pre map the columns from the file to the destination tables. Build numerous Bulk Insert tasks to achieve that.
But not sure if SSIS will let me do that.
Any thoughts on any alternative ways with SSIS?

Seeing as you're using bulk insert, I would think that few transformations are being done during load.

I would think that you might be better off just doing one bulk insert through to an initial holding/staging table.

Once its all in there then you can split it to tables as you see fit.

Guras SSCertifiable Points: 6518 More actions · Answer 2

CKX (3/19/2015)
Guras (3/18/2015)
Can we bulk insert only the desired column from a flat file to a table?
I am using SSIS to bulk insert from a file with more than 200 columns. I am trying to find a way I can bulk insert them to multiples table through SSIS.
The one way I can think is pre map the columns from the file to the destination tables. Build numerous Bulk Insert tasks to achieve that.
But not sure if SSIS will let me do that.
Any thoughts on any alternative ways with SSIS?
Seeing as you're using bulk insert, I would think that few transformations are being done during load.
I would think that you might be better off just doing one bulk insert through to an initial holding/staging table.
Once its all in there then you can split it to tables as you see fit.

I was thinking the same but it's a file with more than 200 fields. So I decided to use Data Flow Tasks in steps so I get the flexibility of mapping this way. Thank you for your response.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Yes you can take one file and in one data flow, insert any column(s) you want to one or more tables.

You could also do this in multiple data flows if you wanted.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Guras SSCertifiable Points: 6518 More actions · Answer 4

Alvin Ramard (3/19/2015)
Yes you can take one file and in one data flow, insert any column(s) you want to one or more tables.
You could also do this in multiple data flows if you wanted.

Thank you, I am doing with multiple DFT for one file.