Menu
In my current project, the database name keeps changing and I do not want to modify my OLE DB Sources each time. Thus I want to set it as a variable. That is, in each OLE DB Source, I could use it as a parameter. However, it seems SSIS doesn't allow database name to be transferred as a parameter. So what should I do to minimize my changes?
I wish to do something like this SQL statement in OLE DB Source:
Select OrderID
From ?.Order
And I just need to modify my variable when the database name is changed. Thanks for any solutions!
247
Right click on your connection manager found in the connection managers pain inside the package and select Properties. Inside the properties window find the property “RetainSameConnection” and set the value to “True”. Now the connection will remain open for the duration of the package. As always thanks for looking!
Open the package in SQL Server Data Tools, and then click the Parameters tab in the SSIS Designer. Click the Add Parameter button on the toolbar. Enter values for the Name, Data Type, Value, Sensitive, and Required properties in the list itself or in the Properties window.
you need to add package configurations to your package and set the database name of the connection manager as one of the configurations
Just right click any blank area on the control flow and select "package configuration" and follow the wizard.
I Suggest you start with a XML configuration because its easier to understand.
142
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
