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

dynamic database connection of bulk insert task in foreach loop Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2014 8:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 6, 2014 4:11 PM
Points: 5, Visits: 20
i am trying to accomplish a task in which bulk insert task which is inside a foreach loop accept dynamic databases names.

step01


step02


step03


step04


step05


step06


step07


step08


step09


i also tried to use the expressions which you can see in step09, but does not work nor after 09 i also tried to use the expression in connectionstring of myDbase inside connection manager in which is:
"Data Source=.;Initial Catalog="+@[User::dBaseName]+";Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package-{B5694B2B-3C04-4B91-01E-B252C756AAD2}LocalHost."+@[User::dBaseName]+";Auto Translate=False;"

but no luck to make the bulk insert to accept the different database name on each iteration or make it dynamic.
Post #1583523
Posted Thursday, June 26, 2014 7:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 6, 2014 4:11 PM
Points: 5, Visits: 20
any help regarding this question?
Post #1586701
Posted Thursday, June 26, 2014 8:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
I don't even know how to spell "SSIS" but perhaps my admission of that will serve as another "bump" for your post. I'd wouldn't mind learning how such a thing would be done, as well.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1586707
Posted Thursday, June 26, 2014 9:37 PM This worked for the OP Answer marked as solution
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 961, Visits: 927
I was able to get this to dynamically insert CSV file into 4 of my local DB's.

I am not sure where you error is but.

1. Set your "Connection Manager" Expression to use Initial Catalog = @dbname. Do not worry about the connection string that will be constructed on its own.

2. In your bulk Task set the expression to (whatever your variable is). Should match step 1.

DestinationTableName = @[User::DbName] + ".dbo.Test"

That should do it.

I am on SSIS 2008R2.
Post #1586713
Posted Sunday, June 29, 2014 7:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 6, 2014 4:11 PM
Points: 5, Visits: 20
Hi Brad.Mason5,
I will try to do as you said and let you guys/gals know what is the result. I do apologize for responding late, i was super busy with other project and almost forgot that i also have a ssis task to do

I do appreciate everyone's input.
Post #1587491
Posted Sunday, July 6, 2014 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 6, 2014 4:11 PM
Points: 5, Visits: 20
i tried as you mentioned in the last post but data is not populating in both database tables, without having any error. is there a way i can get that package which you created and working??
Post #1589697
Posted Sunday, July 6, 2014 2:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 1,257, Visits: 3,638
Quick thought on this, set the Connection string property of the destination connection manager in an expression. Preferably store the entire string in a variable and only alter the database name (if on the same server) part. This works like a charm.
Post #1589698
Posted Sunday, July 6, 2014 4:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 6, 2014 4:11 PM
Points: 5, Visits: 20
i guess i got it..thanks again for all your help and i do appreciate it...
Post #1589702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse