SSIS Package Configuration

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SSIS Package Configuration

SSIS Package Configuration

Rate Topic

Display Mode

Topic Options

Author

Message

zjj

Posted Thursday, October 04, 2012 7:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 6:28 AM
Points: 6, Visits: 28

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.

Post #1368405

Evil Kraig F

Posted Thursday, October 04, 2012 2:06 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 5,688, Visits: 6,141

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.

- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1368692

zjj

Posted Friday, October 05, 2012 12:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 6:28 AM
Points: 6, Visits: 28

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.

Post #1368842

Jason-299789

Posted Friday, October 05, 2012 12:52 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 803, Visits: 2,124

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

Post #1368845

zjj

Posted Friday, October 05, 2012 12:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 6:28 AM
Points: 6, Visits: 28

thanks. will try that

Post #1368846

SpinyNorman

Posted Tuesday, October 09, 2012 2:46 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 11:17 PM
Points: 10, Visits: 37

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.

Post #1370225

« Prev Topic | Next Topic »

Permissions