Hi All, I Have created SQL Server configurations for Dev source and destination files. I wanted to use the same configuration to my Prd environment but able to dynamically change the destination to Production server while executing the package. Is it posible? also I dont understand how the environment varibale and SQL server configurations are related. Could anyone help me? Thanks

Hi, You can check that, it's a great post all about SSIS configuration. http://msdn.microsoft.com/en-us/library/cc895212.aspx ALso you can read about EV here http://support.microsoft.com/kb/310519 I do recommend SSIS package configurations over EV. hope that helps.MCITP - BI 2008 http://asqlb.blogspot.com/

1) Environment variables can store different connection managers' properties' values.In addition to it, they can also store the path to the configuration files. 2)This gives the flexibility to deploy the SSIS in different environments without having to modify the properties of the package manually.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

Hey Thanks Deepak, I have asilly question, I understand the EV must be avilable before I create indirect method config, so what sould be the EV values at the time I create the EV? Thanks,

Thanks for the links Jason, But still some gray are out there, Please see below my scenario I have 3 packages I have used 3 variables on package1 are , flatfile source1, flat file source2, dev SQL Destination Then I have reused the config to other 2 packages. Again the source files are not same for all 3 packages. pk1- flatfile1 source pk2-flatfile2 source pk3- Dev SQL source and the destinations are Dev SQL for all 3 packages. If I wanted to deply this 3 packages to Prd SQL Destination at run time , what is the best way to do so? I could do by creating different config files for Dev & Prd, but I wanted to use the same config file but chnage the destination during deployment or package execution. Thanks in Advance,

Thanks for the links Jason, But still some gray are out there, Please see below my scenario I have 3 packages I have used 3 variables on package1 are , flatfile source1, flat file source2, dev SQL Destination Then I have reused the config to other 2 packages. Again the source files are not same for all 3 packages. pk1- flatfile1 source pk2-flatfile2 source pk3- Dev SQL source and the destinations are Dev SQL for all 3 packages. If I wanted to deply this 3 packages to Prd SQL Destination at run time , what is the best way to do so? I could do by creating different config files for Dev & Prd, but I wanted to use the same config file but change the destination during deployment or package execution. Thanks in Advance, Hi, The purpose of the config file is to create it once and only edit it when you deploy, such if PK1 pointing to \\server1\test when you move the package and the config file you just change it to \\server2\test , so the answer is you can create 2 files or 1 config file, it's up to you, if you don't want to change anything later. and just change the values later while you deploy or replace with the 2nd config file if you'll create 2. for the source files, basically you'll config the datasource for the3 files, so you'll end up with 3 links in the config file. such as: <Configuration ConfiguredType="Property" Path="\Package.Variables[User::PK1.Properties[Value]" ValueType="String"> <ConfiguredValue>\\Ykclnsd\FTPFIles\file1.txt</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::PK2].Properties[Value]" ValueType="String"> <ConfiguredValue>\\Ykclnsd\FTPFIles\file2.txt</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::PK3].Properties[Value]" ValueType="String"> <ConfiguredValue>\\Ykclnsd\FTPFIles\file3.txt</ConfiguredValue> </Configuration> and you just change the name of the server if that's the changeable location , from Ykclnsd to ANyServer. please let me know if you need more examples.MCITP - BI 2008 http://asqlb.blogspot.com/

Thanks Jason, it's really helpfull, Do I need to create the same varibales on all 3 packages? Thanks,

Thanks Jason, it's really helpfull, Do I need to create the same varibales on all 3 packages? Thanks, Yes, unless it's not being used in the other packages. if you didn't create it and it's on the config file, you'll get warnings on you open the package in bids but it's not errors. basically the config file will have values for all the variables even if it's not created in the packages or used. I'm not sure if i'm clear or not? just tell me.. MCITP - BI 2008 http://asqlb.blogspot.com/

Yes you are, just to make sure, I am going to create all the 3 variables in all the 3 packages whether they are used or not within the package, am I right?

Yes you are, just to make sure, I am going to create all the 3 variables in all the 3 packages whether they are used or not within the package, am I right? You could, it's not gonna harm anything. How many config files you'll use for the 3 packages? the best practice is ONE config file, include all the connection strings in it and all the variables' values. Then it's your decision to create the same variables in every package or not, if you didn't create it, you'll be prompted with a warning when you open the package in BIDS, but no problem with that. That's a good article too: http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

Thanks Jason, few more clarifications. see my connections on a single config file <Configuration ConfiguredType="Property" Path="\Package.Variables[User::DevSqlDest].Properties[Value]" ValueType="String"> <ConfiguredValue>Data Source=DevServer;Initial Catalog=db;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-PMI_PPN_Import-{3C58E004-4460-4927-A4DE-BA00D35F3B1D}Devserver.db;</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::PMI_DataSource].Properties[Value]" ValueType="String"> <ConfiguredValue>Flatfile.csv</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::PPN_Datasource].Properties[Value]" ValueType="String"> <ConfiguredValue>Flatfile.csv</ConfiguredValue></Configuration></DTSConfiguration> Q1- Why the aaplication name populated on the destination config (1st one)? Q2- If I can resuse the config between the packages why should I recreate the variables again? Also I have to set the values for the varibales, seems double work. I genarated the above config file, them copied the development folder to production server and chnage the server name to Prd server and run the manifest file. Still complaines could not find the variables. Thanks, Thanks,

Hi Jason, I have successfully deployed the dev config also it worked, but when I changed the config with prd server details its giving warnings, Verufy the package path, Package path can't be found failure importing config file :C:\..........\xxxx.config Any thoughts??

Hi Jason, I have successfully deployed the dev config also it worked, but when I changed the config with prd server details its giving warnings, Verufy the package path, Package path can't be found failure importing config file :C:\..........\xxxx.config Any thoughts?? Yep, that means something is different, just make sure the paths are exactly the same, file names are exactly the same, SQL tables are exactly the same data types and width.MCITP - BI 2008 http://asqlb.blogspot.com/

Thanks Jason, few more clarifications. see my connections on a single config file <Configuration ConfiguredType="Property" Path="\Package.Variables[User::DevSqlDest].Properties[Value]" ValueType="String"> <ConfiguredValue>Data Source=DevServer;Initial Catalog=db;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-PMI_PPN_Import-{3C58E004-4460-4927-A4DE-BA00D35F3B1D}Devserver.db;</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::PMI_DataSource].Properties[Value]" ValueType="String"> <ConfiguredValue>Flatfile.csv</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::PPN_Datasource].Properties[Value]" ValueType="String"> <ConfiguredValue>Flatfile.csv</ConfiguredValue></Configuration></DTSConfiguration> Q1- Why the aaplication name populated on the destination config (1st one)? Q2- If I can resuse the config between the packages why should I recreate the variables again? Also I have to set the values for the varibales, seems double work. I genarated the above config file, them copied the development folder to production server and chnage the server name to Prd server and run the manifest file. Still complaines could not find the variables. Thanks, Thanks, App name is a default behavior to appear, unless you evaluate the connection as expression, create a string variable to hold the connection string and add that variable as expression on the connection string. also variables has to be created, but gets its value from the config file, you don't need to initialize it anymore, just create it, close the package and re-open it and you'll have the values populated.MCITP - BI 2008 http://asqlb.blogspot.com/

Thanks Jason, It was very helpful, one more thing, I created dev server connection string withing package, and changing the prd server details on the config file. Do i need to have prd connection string also to be created with package be fore deploy the package?? Thanks,

Thanks Jason, It was very helpful, one more thing, I created dev server connection string withing package, and changing the prd server details on the config file. Do i need to have prd connection string also to be created with package be fore deploy the package?? Thanks, It should be only one server string, and you edit the strings value whenever you move the package. so if the connection is pointing to 'serverA', when you move the package, you just need to change the value to 'serverb'MCITP - BI 2008 http://asqlb.blogspot.com/

experiancing new issue, I have saved the connections strings to 3 different variables on pk1 then reuse the varibales within pk2 & pk 3 I just closed BIDS and open, there are no values for the variables, what ever i saved all missing. any idea??? Thanks,

experiancing new issue, I have saved the connections strings to 3 different variables on pk1 then reuse the varibales within pk2 & pk 3 I just closed BIDS and open, there are no values for the variables, what ever i saved all missing. any idea??? Thanks, Yep, you need to make sure that you used the VARIABLE's value in the configuration file, looks like you didn't bring the VARS values into the config file.MCITP - BI 2008 http://asqlb.blogspot.com/

How? Coz I created the Var assign the values, saved the var to connection manager expressions, am I missing some thing?

How? Coz I created the Var assign the values, saved the var to connection manager expressions, am I missing some thing? Here, i've uploaded a screenshot, make sure to bring the VARS into the config file,so SSIS knows that it'll find the value in the config file http://www.box.net/shared/mdst24mrc8 MCITP - BI 2008 http://asqlb.blogspot.com/

Hi Jason, Thanks alot, but I have done exactly the same.

Hi Jason, Thanks alot, but I have done exactly the same. I'm sorry, explain to me again what's the issue now?MCITP - BI 2008 http://asqlb.blogspot.com/

Ohh man, Im tired, Now i could see all the variables and values in the config file, but when I execute the package in Execute package utility, execute succesfully, there are no errors, but I dont see any data in the destination tables. Sorry to bother you Jason.

Ohh man, Im tired, Now i could see all the variables and values in the config file, but when I execute the package in Execute package utility, execute succesfully, there are no errors, but I dont see any data in the destination tables. Sorry to bother you Jason. No it's ok, just it's takes time to get the hang of it, but after that you'll master it. and it also depends on the environment and what VARS are shared between the packages.MCITP - BI 2008 http://asqlb.blogspot.com/

Hey Jason, No worries, it was my bad, I have change the dest table, and was checking at the wrong table. It seems working fine, My mistake was, kept the config file in the default path, which was not same as on dev environment. Great stuff, Thanks alot Jason, appreciate your help.

Cool :)MCITP - BI 2008 http://asqlb.blogspot.com/