Dynamic Connection Strings in a Scipt

I am trying to split a .csv file on a week+location key. As there are 500+ locations and 52 weeks in a year this is not a manual task.

I have a Script component written but need to dynamically open/close the connection manager changing the connection string in between, to the Week+location key as the filename.

It has to be do-able because the ForEachLoop does it. But how do I?

You'll most likely want to use a ForEach Loop, variables and property expressions.

This blog post should help you out - Looping over files with the Foreach Loop

HTH,

~Matt

|||That would be fine if I was trying to concatenate many to one, I use the ForEachLoop a lot, but I am trying to split one into many. I can't see how to use an FEL for that, or am I just being dumb?|||

I see two approaches you could take:

1. Use a script task (in the control flow, not the data flow) to split up your CSV file into multiple files, then process them all using a dataflow inside of a for each loop.

2. Continue using your script component, but use a foreach loop to pass in the week + location key you want to process, and process them one by one.

~Matt

|||Maybe this post might give you some ideas: http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx|||

Ah, thanks John! I hadn't see that entry before. I'll keep that link handy for future reference.

~Matt

|||Thanks for the help.

The technique I ended up using has a script task inside a for each loop which sets the connect string before a dataflow connects using the new connect string and processing each file. Simple when you have a night's sleep!!
|||Can you please tell me how to set the connectionstring in script task dynamically ? I need to set the initialcatalog property of connectionstring and loop over multiple databases to transfer data in foreach loop in SSIS package. So I have taken the database names in a collection and looping each of them. However when I create another script in it, the visual studio breaks down and says you have encountered unhandled exception. Can you throw some light over it ?|||Can you post the script your are using? Sounds like you might have a bug in it.