For Each Loop with Bulk Insert in SSIS

Question

For Each Loop with Bulk Insert in SSIS

jyoti_bhatt

SSCommitted

Points: 1950
More actions
October 22, 2009 at 8:49 am

#215788

Hi,
I am trying to get a SSIS package to load multiple csv files from a folder into a sql table using bulk insert. I am unsing ForEach Loop Container and the Bulk Insert Task but cannot get it to work, I think its an issue with setting the source connection?
Can anyone advise please??
Many Thanks

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

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

Alan. T. Hall of Fame Points: 3221 More actions · Answer 1

I don't know where the issue is but when I was learning about these I would put a script control inside the ForEach container and examine the variable data by sending the data to a message box. This will make sure you are assigning the output of the loop to a variable.

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 2

Hi Alan,

Thanks for your response. Could you possibly advise how you'd set the source connection in a bulk insert task that is within a foreach loop? If I assign an existing file then it keeps looping through that particular file, if I assign a expression for source connection as the filename I'm getting an error message "The specified connection "" is either not valid, or points to an invalid object. To continue, specify a valid connection."

If you could kindly explain how you'd set the source connection in bulk inset within foreach loop that will be much appreciated?

Many Thanks

Alan. T. Hall of Fame Points: 3221 More actions · Answer 3

I never used the bulk insert task in the 1000+ packages I have written. I seem to recall a lot of issues in the forums so I skipped it. Instead I start a data flow task and use the file source and the OLE DB destination. Additionally, I usually had some processing to do so I could perform this en route to the SQL Server.

I hope there is someone else who can help you with the bulk insert. The other place I would try in the SSIS forum on MSDN it is much more hit or miss than here but occasionally you can find a MVP who gives really good information.

Sorry I thought of one other item. The file connection manager requires a file to be at the location when you set them up so you may need to create the files at the location even it they are dummies to set it up.

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 4

I do use data flow tasks but wanted to know how to get the bulk insert working for multiple files. I have created the dummies files thanks for the thought and for your response. I'll try posting a message on MSDN.

Thanks once again.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 5

Sorry I couldn't be more help, but if you do gt it working well please post any tricks.