Thursday, May 17, 2012 7:44 PM
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.
Thursday, May 17, 2012 7:51 PMModeratorIt 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
- Marked As Answer by SSISNewbie345 Thursday, May 17, 2012 9:40 PM
Thursday, May 17, 2012 7:58 PM
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 ....
- Edited by SSISNewbie345 Thursday, May 17, 2012 7:59 PM
Thursday, May 17, 2012 9:21 PM
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.