Sorry again i m getting same result . Please help me out from this problem .Because this is the problem which i m more concerned about it will help in future also. when i will move my package in another server i dont have to change all 500 component connection String in Data Flow instead of this what i will do I will just change the connection String . Above i have mentioned each and every step i have done . Can i do Such kind of thing in SSIS ? am i doing or thinking something wrong.

Hello Everyone, I am Still facing problem in package configuration. I have created one SSIS package Demo which consist of 500 tables or 500 data flows in that data flows i have used(OLEDB Source,OLEDB Destination) there are two different connection string name 1)Source 2) Destination which is used by (OLEDB Source,OLEDB Destination) 2)Now i have Used package configuration in this package. i have used configuration type:XML Configuration n i that XML file two connection Connection String of 1)Source 2)Destination is defined 3)What i have done in XML file is as follow 1)XML file created by using package configuration u can see two configuration String here <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="alex ford" GeneratedFromPackageName="Package" GeneratedFromPackageID="{00992C3F-114C}" GeneratedDate="7/13/2011 3:06:00 PM"/> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Connections[172.19.5.55.CHJSCM.psa].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=172.19.5.55;User ID=psa;password=***********;Initial Catalog=KHJSCM;Provider=SQLNCLI.1;Auto Translate=False;</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[DB.AppDB.csa].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=182.17.6.43;User ID=csa;password=*********;Initial Catalog=AdvetureWorksDW2008R2;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue> </Configuration> </DTSConfiguration> 2) I have changed second configuration string u can see below second configuration changed from above second <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="alex ford" GeneratedFromPackageName="Package" GeneratedFromPackageID="{00992C3F-114C}" GeneratedDate="7/13/2011 3:06:00 PM"/> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Connections[172.19.5.55.CHJSCM.psa].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=172.19.5.55;User ID=psa;password=***********;Initial Catalog=KHJSCM;Provider=SQLNCLI.1;Auto Translate=False;</ConfiguredValue></Configuration> < Configuration ConfiguredType="Property" Path="\Package.Connections[FTWZPNLDB.AppDB.fsa].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=FTWZPNLDB;User ID=fsa;password=***********;Initial Catalog=AppDB;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue> </Configuration > </DTSConfiguration> 3)When i am doing this change in connection String as done above in Second connection String i have changed than my package is not taking connection String from XML file its taking Connection String which i have set in OLEDB Source and OLEDB Destination which is defiend in first step which i dont want. pls suggest me some solution or provide me some link for this i want tht my package should read connection string on run time (Dynamically) from XML File I want to see the real use of SSIS package configuration.which not given in any of examples. The examples which are given for package configuration just tells that how to configure connection string for packages Its not given over there that how to use these Package Configuration. I am searching solution for this problem from month but not yet resolved Please Provide some solution or example or link so that i can understand it quickly Hope now u people have understand my problem

did you run the package after changing the configuration values? package will read configuration at the time of running.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Ya i have Run my package but my package is not reading connection String froXML file. My package is reading connection which i have defined in step one OLEDB Source/OLEDB Destination This is my main problem.

Did you activate the Configuration in your package? If you run your package from BIDS, is there any message about the Configuration a'la: "Package tried to load the Configuration blabla"? Please check.

yes i have activate the Configuration in my package. if i run my package from BIDS no there is no message. My package is running properly but as i told u above its taking hard coded value not from XML file

weird, let's start from scratch create a package variable, and then add this variable in the configuration. then change it's value in configuration file and run the package. put a breakpoint at the beginning of first task in ssis package on PreExecute event and check the value of that variable at runtime. Does the value of variable fetched from configuration file or not?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I Dont how to use variable in this situation right now i was using XMl config file directly. Can u please tell me how to do this process

go to menu->ssis->variable in the variable window, add new variable name it for example MyVariable, set data type as Int32, with no default value then go to ssis->Configuration, and edit previous configuration you already have, and add this variable there. then in first task of control flow, right click , click edit breakpoints, check a break point on PreExecute and then change the MyVariable value in xml configuration file, and run the package, when package running, it will pause at preexecute of first task, go to locals window in BIDS, and find the User::MyVariable there and check it's value. did it picked value from configuration or not?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

then go to ssis->Configuration, and edit previous configuration you already have, and add this variable there. How to add the variables there?

then go to ssis->Configuration, and edit previous configuration you already have, and add this variable there. How to add the variables there? click on the configuration first, then click edit, on the Select Configuration type step click next, on the Select Properties to Export step , expand MyVariable in the objects pane, and check the Value property, then continue the wizard and finish ithttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

U r telling me to edit on the configuration .but this edit will take place in XML only i will get value property of MyVariable in XML only . I hav done that also .I dont know i am doin right or not . when package running, it will pause at preexecute of first task, go to locals window in BIDS, and find the User::MyVariable there and check it's value. did it picked value from configuration or not? now where to check My variable value .

now where to check My variable value . in the locals pane, at the runtime you will see many panels, watch, locals, find results,.... locals maybe at lower part of BIDS, maybe you should click on it to show it up, and there you will find a variables hierarchy,all system and user variables listed there, find the MyVariable there and check it's value.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I have used watch,locals also but i m not seeing any change in the valuse of User::MyVariable .It remains 0 only . I dont know how the vlue of this variable will change. Please reply I m waiting 4 ur reply

I dont know how the vlue of this variable will change. Did you changed the value in xml configuration file? could you paste xml configuration content here?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

What value i have to change in XML can u please tell me <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="alex ford" GeneratedFromPackageName="Package" GeneratedFromPackageID="{00992C3F-114C}" GeneratedDate="7/13/2011 3:06:00 PM"/> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Connections[172.19.5.55.CHJSCM.psa].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=172.19.5.55;User ID=psa;password=***********;Initial Catalog=KHJSCM;Provider=SQLNCLI.1;Auto Translate=False;</ConfiguredValue></Configuration> < Configuration ConfiguredType="Property" Path="\Package.Connections[FTWZPNLDB.AppDB.fsa].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=FTWZPNLDB;User ID=fsa;password=***********;Initial Catalog=AppDB;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue> </Configuration > <Configuration ConfiguredType="Property" Path="\Package.Variables[User::MyVariable].Properties[Value]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration> </DTSConfiguration>

You have to change this <Configuration ConfiguredType="Property" Path="\Package.Variables[User::MyVariable].Properties[Value]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration> to <Configuration ConfiguredType="Property" Path="\Package.Variables[User::MyVariable].Properties[Value]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration> If you have a zero in your variable and your Configuration you can't verify the Configuration ist load because the value ist always the same Another point: If you don't replace the Passsword with "*" for posting here, you have to replace the stars with your correct Password. Notice: An error while reading the Configuration throws no error in executing the package but use the original values in Connection Managers.

<Configuration ConfiguredType="Property" Path="\Package.Variables[User::MyVariable].Properties[Value]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration> I have changed the value as told by you than also nothing is happening. please tell me some solution for this .

do you have any other configuration ? or just one configuration?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

No I have given you all the information what i have done till now .

weird results. could you try with a new package? also with new configuration with the scenario of package variable test as I described above? let me know the result,http://www.rad.pasfu.com My Submitted sessions at sqlbits.com