Bulk Insert Task Within ForEachLoopContainer???

Question

Bulk Insert Task Within ForEachLoopContainer???

SSIS\SSRS\SSAS

SSCertifiable

Points: 6038
More actions
May 22, 2008 at 11:55 am

#122346

Hi to All,
My First Question--Can we use Bulk insert task within the foreackloop Container???? If "NO" U can stop rt here or provide alternatives..
If YES----I need help.
What i am trying to do is very simple. Loop over a folder find all .txt files and do bulk insert into SQL table. i think i have everything setup in right way.
I have one bulk insert task inside my foreach loop. Enumerator is foreachfileenumerator. i have a varibale-filetobeloaded.
in my file connection properties i have connection stringexpression using this variable.
The erro i get is flat file connectionis invalid or pointing to an invalid object.
Thnaks, in advance if u coul dplz help me to do this.

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

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

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 1

I see no reason why you couldnt bulk insert within a for each enumerator (please dont lynch me, 3 weeks of SSIS is hardly enough to deliver expert opinion).

I would have a variable called strFileName, which I would add to the variable mappings section of the enumerator

I would also have a variable which has the folder name to traverse on, which I would add under the expressions section (directory name). Simply to assist when porting the package elsewhere

Then I would add this same variable to the connectionmanager of the file, under the expressions (connectionstring).

This should enable you to pass the file from the enumerator to the connection manager each and every time.

Good Luck

~PD

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 2

Dear Pu.........,

My assumption was not a statement, it was a question becoz i was failing. Finally with some tweaks i was able to do it. There was nothing wrong in the approach of mine. Simple stupid mistake. I forget to put the value in the variable for the file path(i guess this was my mistake), so before even executing the bullk insert it was valiadting the file connection, and was throwing error---invalid file connection or the file connection is pointing to and invalid object or object that doesn't exist.

Anyway, thanks to u for ur time

and to all out there.

Andy Ainsworth Newbie Points: 7 More actions · Answer 3

Hi please could you provide code for running bulk insert for each file in a folder. Have been looking everywhere to help figure it out myself

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 4

Andy, you don't need a code. Brimg the foreachloop container and in the properties of it, make sure it returns full file path. create a variable and map this to this . bring bull insert and define connections. map this connections to that variables that you created. so everytime you reterive the full file path from folder that will be your connection string.

THANKS.

john.thurgood Newbie Points: 3 More actions · Answer 5

SSIS\SSRS\SSAS (5/23/2008)
Dear Pu.........,
My assumption was not a statement, it was a question becoz i was failing. Finally with some tweaks i was able to do it. There was nothing wrong in the approach of mine. Simple stupid mistake. I forget to put the value in the variable for the file path(i guess this was my mistake), so before even executing the bullk insert it was valiadting the file connection, and was throwing error---invalid file connection or the file connection is pointing to and invalid object or object that doesn't exist.
Anyway, thanks to u for ur time
and to all out there.

I'm a newcomer to this concept; would it be possible to share your .dtsx package so I could see how to do this?