Hello,
We need duplicate a large number of SSIS packages , changing only server connection and database name inside the OLE DB Source sql command. Is there any easy way to do it , I mean without opening every package individually
Thanks,
BeginnerInBiztalk
This can be achieved by using configurations. With configurations you can:
¡¤ Save property values outside of packages
¡¤ Apply saved property values to packages at runtime
¡¤ Allow easy migration between environments
Check BOL for details about implementing configurations.
If you already have a large set of existing packages with different implementations where you need to make these specific changes (and configurations are not the solution you need) then you can use the SSIS .NET API to make the necessary changes.
Here's the an entry point into the API documentation on MSDN: http://msdn2.microsoft.com/en-us/library/ms136025.aspx
Thank you for your response. Usually in side the OLE DB Source we use sql statement text option that contains sql code. Most of the time it has joins to the tables in several databases that are not necessarily in the OLE DB Connection manager. This means that even if we change connection managers using package configuration we still need to open OLE DB source in the design mode to make changes in our sql code. Am I correct Is there any solution for that
Thank you .
BeginnerInBiztalk wrote:
Thank you for your response. Usually in side the OLE DB Source we use sql statement text option that contains sql code. Most of the time it has joins to the tables in several databases that are not necessarily in the OLE DB Connection manager. This means that even if we change connection managers using package configuration we still need to open OLE DB source in the design mode to make changes in our sql code. Am I correct Is there any solution for that
Thank you .
You'll have to test this in your specific circumstances, but I'm tempted to say "no" because:
1) The entire SELECT statement (including the cross-database JOINs) is going to get sent to the OLE DB connection, and it's up to the server (and its configuration) to handle it. It will work or fail regardless of whether you've "refreshed" the package in the designer.
2) The SELECT statements need to have a 100% interface matching (same columns, same order, same data types, etc.) or else you're going to have metadata issues further on in the package; I've been assuming that this is not the case in your scenario and that your changes will not break this implied metadata interface.
Does this seem to apply to your specific problem If I'm missing something here, let me know...
You do not have to open the OLE DB source in design mode when using configurations. Here is the idea.
This will work as long as your column names and data types are consistent. The benefit of this approach is that you can go from having many packages to having one package. Apply different configurations as needed.
FYI, Matthew's suggestion will work great if you want to do it programmatically.
FYI, Matthew's suggestion will work great if you want to do it programmatically.
But Danny's suggestion is the better one - Configurations are the ideal technology to use here, and if I were in your shoes I would try to spend the time now doing it right, rather than trying to do it now...
This is what I thought the answer would be.
Basically in the data flow in the data source we have SQL statements that reference a number of databases. When we distribute our SSIS solutions for our clients, the only thing that initially changes is database names . So I was hoping there is some sort of ¡°replace all¡± solution or a tool that can do global replacement in the package.
Thank you for your help.