April 1, 2019 at 11:00 am
Hello
Using SQL 2016
I have inherited a project that has a number of packages
Rather than create a project connection, seperate identical source and destination package connections have been created
So, they all have the same name but are specific to the package
I would like to have a project source and destination connection and use this in all projects
Thought that would be pretty straight forward
If I convert one of them that's all it converts - fair enough and makes sense
What I cannot do though is amend the existing ones to use the new project ones
I cannot even use the project connection source or destination in one of the existing packages as it isn't available
i.e. edit a Control Flow Source and look at the OLE DB connection manager drop down - this only shows the package connections
Feels like I'm doing something wrong here
Again, thought this would be straight forward
Historically, I've just created project connections then created package that use them with no issue
Seems like there's an issue if they already exist at this lower (package) level
Any thoughts?
Thanks
- Damian
April 1, 2019 at 12:48 pm
This was removed by the editor as SPAM
April 1, 2019 at 1:41 pm
The short answer is that there's not an easy way to convert all of those package connections with the same name to a new project connection.
The longer answer: When you change a connection from a package connection to a project connection, it doesn't really convert it - it actually creates a new connection. However, the UI hides this from you, making it appear that it's just an in-place change to the existing connection. To make this change for every package in the project, you'd need to use the Convert to Project Connection function on exactly one of the packages, and then modify each of the other packages to use that new connection.
Making this more difficult is the fact that there is a bug (or is it a feature?) in SSIS that causes some weird behavior if you have a package connection and a project connection with the same name. If you have a package connection named MyConnection, and a project connection with that same name, it will appear in the UI as if only the package connection exists.
If you've got a lot of SSIS packages in the project that would need to be changed, it might be worth creating a solution in Biml, or even hacking the underlying SSIS XML to do this (it ain't pretty, but it can be done). But if the number of packages is fairly modest, it's probably going to be easier to do this manually. One bit of advice I would offer either way is to change the name of the new project connection to avoid the naming collision oddness I mentioned above.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
April 1, 2019 at 1:58 pm
Thanks Tim
Shame really and, as you say XML edits seems to be a way
Really surprised there isn't an easier option
I think in this instance I'm going to do it manually and I've done it this way on a 2 package project before i.e. small
Just that this one's a little bigger
- Damian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply