We have requirement to move the data from Oracle to Sql server. Inside the package we have a dataflowtask to pull the data from oracle (development server) and will be loaded to SQL server. This works fine when i run on development server. Please let me know how to make the oracle source dynamic so that it will be pointed towards the oracle production server when i run the package on Prod. Thanks

It is very easy to introduce package configurations which brings tremendous flexibility to your packages. With a package configuration you can edit your package properties, variables, connections and the properties of your control flow tasks (termed executables) at run-time. Note that you cannot edit the properties of your data flow components. On the menu bar, point to SSIS, Package Configurations or right-click on the control flow design surface and select Package Configurations. In the Package Configurations Organizer click Enable package configurations and click Add Click through the welcome screen and in the Configuration Type combo select XML Configuration File. In the space for Configuration file name type C:\PackageConfigurations\Environment.dtsConfig and click Next In the object tree browse to Connections.Destination.Properties and check the InitialCatalog & ServerName properties. Click Next Give your configuration a name and click Finish Arthur My Blog

This is an existing package which we are modifying.... we are using the package configurations as Environment variables and variables. I have actually created a variable @varsource = select * from Development and updated the @varSource = select * from Prod at table level in the Prod. I have return an expression @data flow level. Let me know where to write the expression and which property to use. Thanks alot ....

If I understood your question correctly, this might help you: You can use Host_Name for your server name. DROP an Execute SQL task to the control flow, and then Create a variable, say, User::SrvrNm Now In the Exec SQL task use the following code: DECLARE @SrvrName VARCHAR(100) SELECT @SrvrName = Host_Name() SET ? = @SrvrName Under the variable mapping of Exec Sql task, map it as Output to the SSIS variable SrvrNm Now, create another SSIS variable and say User::SrcQry (set evaluate as expression to True) Now under the expression set it to: "SELECT * FROM " + <Drag the SrvrNm variable here> + "DbNm.SchemaNm.TableNm " This should solve your problem. Also, remember to set a dummy value, since, DFTs need to be mapped upfront. However, like ArthurZ mentioned above, your best bet/way would be to use Config files. Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.