package configuration please help

I would like to set package configuration.But dont know how to.

What i want to achieve is that i want to change connections based on test ,development and production environment of the same package...

How can i do that without me changing the connections manually to each task based on environment..

using package configuration i dont know how to...Please let me know.

http://www.sqlis.com/26.aspx|||

http://msdn2.microsoft.com/en-us/library/ms167153.aspx

http://rafael-salas.blogspot.com/2007_01_01_archive.html

|||

>>http://rafael-salas.blogspot.com/2007_01_01_archive.html

Form this link is it like i have to create one table in sql server database which has connection string with a particular connection

so is this connection applied to all the tasks like lookups ,oledb commands,oledb destinations etc...and in my cast the text fine connection dosent change at all....so what do i do...

|||i dont find InitialCatalog & ServerName properties.. ....from the steps in this tutorial........|||FYI - you don't create the table manually, SSIS will do it for you. (That is if I'm assuming correctly what you are talking about and what Rafael wrote.)

When you create a new SQL Server based package configuration, it will create the table for you if it doesn't exist.

(I can't get to Rafael's site at the moment as we have a mis-configured proxy that blocks his blog page......)|||

sureshv wrote:

i dont find InitialCatalog & ServerName properties.. ....from the steps in this tutorial........

Those are found on database connections.|||

sureshv wrote:

>>http://rafael-salas.blogspot.com/2007_01_01_archive.html

Form this link is it like i have to create one table in sql server database which has connection string with a particular connection

so is this connection applied to all the tasks like lookups ,oledb commands,oledb destinations etc...and in my cast the text fine connection dosent change at all....so what do i do...

Phil is right, the table is created by SSIS, if it does not exist. Every row in that table represent a configuration value to be applied to a specifc property in a package's object. A row in that table looks like

ConfigurationFilter ConfiguredValue PackagePath ConfiguredValueType DBConnection-MARINER_EDW Data Source=xxxxx;Initial Catalog=xxxx;Provider=SQLNCLI.1;Integrated Security=SSPI; \Package.Connections[MARINER EDW DS].Properties[ConnectionString] String

Where ConfigureFilter is what SSIS uses to know which row to pick from the table; ConfiguredValue is the value to be used when overwriting the property value; PAckagePath will tell SSIS which property will be override.

In your case that property might be the connection string of a connection manager. Then all other objects/tasks using that connection manager, like a lookup component, should pick up the configuration value from the table.

Does this make sense?

|||

thanks phill...i was able to view his page though...

Let me explain my situation clearly...

here goes the process...

I have a package which has two task

1. execute sql

2. data flow

With in data flow..

i have a flat file connection to my text file

i update or insert records to my destination.

i have lookups,oledb commands,oledb destinations...

so far i am using oledb command...

when i insert or update what i do is insert few fields of incoming record in one table get the identity field number and insert rest of the data and the identity field to another table...which uses connection1

now then i take identity fields of the records inserted from above two tables and insert them to another table in a different database with different connection 2...

all these are in the demo environment...

Now my question is i want to using the same package to run but one change which i want to make is change the connection1 and connection2 to connection 3 and coneection 4.Is that possible using package configuration...

please let me know...

|||

sureshv wrote:

Now my question is i want to using the same package to run but one change which i want to make is change the connection1 and connection2 to connection 3 and coneection 4.Is that possible using package configuration...

please let me know...

Yes, this is what we've been illustrating to you.

You need to chose the database connectionstring property (that's all I use) of the connection manager object you are using in your data flows. Storing the connectionstring property in the SSIS package configuration table will allow you to change the connections whenever you wish.

Note, if you use the same connection manager object in your package configuration setup as you do in your data flows, this isn't going to work -- it will never change from the server defined in the connection manager setup. You should create a separate connection manager object for the package configuration table and store its configuration in an XML config file. (Again, that's what I do.)|||

It am trying to get the point but it would be great with an example..

Ok one last bit of confusion here

i have configured as u suggested me but dosent seem to work...Let me make it clear on what i do...

Lets take two conections

Connection 1

Connection 2

These are used in the data flow

So in the control flow i am setting it this way...let me know if its right

Package configuration->enable package configuration->Add

Configuration type->xml file

Configuration File->file.dtsConfig

with in select properties to export

under Connection Managers->should i select connectionstring of connection1 and connection 2 or should i select connectionstring of connection 3 and Connection 4

It would be very helpfull if u let me know about it...

Thanks in advance...

|||

sureshv wrote:

It am trying to get the point but it would be great with an example..

Ok one last bit of confusion here

i have configured as u suggested me but dosent seem to work...Let me make it clear on what i do...

Lets take two conections

Connection 1

Connection 2

These are used in the data flow

So in the control flow i am setting it this way...let me know if its right

Package configuration->enable package configuration->Add

Configuration type->xml file

Configuration File->file.dtsConfig

with in select properties to export

under Connection Managers->should i select connectionstring of connection1 and connection 2 or should i select connectionstring of connection 3 and Connection 4

It would be very helpfull if u let me know about it...

Thanks in advance...

You should choose the string connections you want to change (eg. from dev to prod) at run time; so if you only want to change 1 and 2; that should be your selection...

|||so in the package configuration i select connection string of connection 1 and 2...but it dosent work....it does not go to insert in the demo database instead it inserts into dev only...|||

Ok, something is not properly set up.

Just to recap; how are you implmenting package configurations? Using only an xml file? or using a SQL table-xmlfile combination?

either way; make sure the configuredValues points to the DEMO env (the connection string is correct).

Also, check the log or progress tab when running the package to see if there is any warning about package configurations not taking place....

|||

ok rafael...

it dosent seem to work at my end...

let me make myself clear...

Under control flow i have data flow task and with it i have oledb destination right now the destination tbale connected is dev environment

i have one more connetion setup which is demo environment but right now none of my tasks uses it..

Now i set up package configuration

i use xml file

i select the connetion string of the demo environment...

now i run my package...

i dont have any error or warning

the data gets stored in the development...is this the way i got to set..

Please help me out...