Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk Insert Task Within ForEachLoopContainer???


Bulk Insert Task Within ForEachLoopContainer???

Author
Message
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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.
pduplessis-723389
pduplessis-723389
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 400
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
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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
Andy Ainsworth
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 28
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
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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
john.thurgood
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search