November 8, 2006 at 10:02 am
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target
containers.
3) We have a master execute package which contains series of execute packages in relational order.
4) When we execute this master package, we should be able to dynamically specify different source and connection
strings for all packages.
5) Can we do it with dtexec option /Conn[ection] IDOrName;ConnectionString
We know what to give for ID or Name but what do we give for ConnectionString. Will it be the source/target one? Can we specify both source and target ones with which the data flow packages get executed.
November 8, 2006 at 12:25 pm
"4) When we execute this master package, we should be able to dynamically specify different source and connection
strings for all packages.
5) Can we do it with dtexec option /Conn[ection] IDOrName;ConnectionString
We know what to give for ID or Name but what do we give for ConnectionString. Will it be the source/target one? Can we specify both source and target ones with which the data flow packages get executed"
I'm not quite sure if I understand 100% of what you want to do, however it sounds like you could use the Package Configurations property in SSIS to build an XML file with for one pkg's source and connections. Then for each pkg that you want to point to a different source, edit that XML file with those values. For example, generate an XML config file called Development.dtsConfig. Then edit that file with the connections for your Test box and save it as Test.dtsConfig.
If you are running the pkg as a Job from SSMS, all you'll need to do is point the job to the correct XML File. And that config file can be used to change the sources and destinations for everything from Database servers to flat files.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply