October 4, 2012 at 7:45 am
Hi,
I have created an SSIS package that extracts data from a flat file source, then loads the data into an SQL Server database. I would like to use package configurations to change the destination dynamically, i.e change the database to a new one as the destination. please help. I have tried the XML config type, but to no avail.
October 4, 2012 at 2:06 pm
Can you be a bit more explicit as to what dynamics you're intending here, and what exactly you've tried?
A configuration loads at the beginning of the run, and is set then, and won't change later. You do this via the configuration editor and change the information in the data source in the package.
If you want truly dynamic modifications (such as looping to multiple databases), you'll need to use a ForLoop or a ForEachLoop, a package variable, and expressions.
What, exactly, are you intending to do? I think you were just trying to pack as much information in as quickly as possible and tripped me up understanding your intent.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2012 at 12:28 am
I have a flat file source and want my destination to be an OLE DB. That works fine. I used package configurations to create an XML configuration file. I want to be able to change the destination to a new table and database destination dynamically, thus, only making changes to the configuration file.
I have tried to make changes to this XML node of the file:
<ConfiguredValue>Data Source=.;Initial Catalog=Test;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package1-{E909490D-9F3C-4979-8014-63A0FAD144D6}LocalHost.Test 1;Auto Translate=False;</ConfiguredValue>
BUT, this still does not work.
Please assist.
October 5, 2012 at 12:52 am
I've dont exactly this on a previous projcet, except that the source was a differnt database.
The only way I could get round it was to execute a child package passing in the Servername and Catalog through package variables.
Such that you have
Outer Package :
two variables called Servername and Catalog
For EACH DB looping round a record set
Populate Servername and Catalog
CALL Child Package
Reapeat for each record
Child Package :
1.Add two variables with Names the same as the Outer package (case sensitive)
2.Set up an configuration using to use Parent Variables
3.Using Expressions on the data connecter set the Servername = Servername variable, and Initial Catalog to the Catalog
4.Add DFT
In the child package dont forget to set default for the variables so you can test.
That should do exactly what you want.
For the first few operations I would put in a Script task as well that returns the values of the variables in a message box, so that you can check they are changing in line with the outer package.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 5, 2012 at 12:56 am
thanks. will try that
October 9, 2012 at 2:46 am
Jason's suggestion sounds spot on.
I would just add that you need to be careful with the metadata. If the destination table structures aren't the same then you will get warnings/errors about the metadata at runtime.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply