Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert Task Within ForEachLoopContainer??? Expand / Collapse
Author
Message
Posted Thursday, May 22, 2008 11:55 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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.
Post #505379
Posted Friday, May 23, 2008 4:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, 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
Post #505744
Posted Friday, May 23, 2008 8:03 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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.
Post #505873
Posted Tuesday, July 15, 2008 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 8:17 AM
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
Post #534419
Posted Tuesday, July 15, 2008 10:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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.
Post #534569
Posted Friday, April 19, 2013 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:00 PM
Points: 1, Visits: 11
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?
Post #1444307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse