dynamic database connection of bulk insert task in foreach loop

Question

Post reply

dynamic database connection of bulk insert task in foreach loop

hussainismail

SSC Enthusiast

Points: 174
More actions
June 18, 2014 at 8:25 pm

#287008

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.

Viewing 8 posts - 1 through 7 (of 7 total)

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

hussainismail SSC Enthusiast Points: 174 More actions · Answer 1

hussainismail

SSC Enthusiast

Points: 174

June 26, 2014 at 7:26 pm

#1725316

any help regarding this question?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 3

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.

hussainismail SSC Enthusiast Points: 174 More actions · Answer 4

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.

hussainismail SSC Enthusiast Points: 174 More actions · Answer 5

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??

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

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.

😎

hussainismail SSC Enthusiast Points: 174 More actions · Answer 7

i guess i got it..thanks again for all your help and i do appreciate it...