SQL 2014 SSIS Dynamic Connection managers issue
-
I have a strange one here which has me and few others a little bit stumped.
We have an SSIS template package which has been the base for all SSIS packages used within our environment, this was originally developed in BIDS and upgraded to work in SSDT.
The package contains a script task which takes each connection manager connection and retrieves the connection string from a view called SSISConnectionConfigurations, this connection string is used to change the connection string of each manager at run time.
So for example the package is developed against a DEV server so if it had a connection called STAGE it would have the default values of DBDEV for the server and STAGE for the database. When the package is executed it will over write the connection string with the connection string retrieved from the table, the idea being when we deploy between DEV \ QA \ PROD the connections only need to be maintained in the table for each target environment.
Now the problem we have when executing these packages against a SQL 2014 instance is the dynamic connections are not updating at run time when the package is executed via a SQL server agent job, via the command line dtexec or via SSMS using execute package under SSIS, it does however work when executed in SSDT on the same host and as the same user as the SQL server agent service runs under. There is logging enabled and the script task is throwing no errors, its also definitely being ran.
Has anyone else came across this issue in SQL 2014? For reference the server is SQL 2014 Developer edition (12.0.5590.1) and the packages have been developed in SSDT version 14.0.23107.0D14REL.
We have tried a number of things such as swappping between 32bit and 64bit execution modes, running under different AD accounts with administrative privileges both on Windows and SQL to rule out perms issues, creating a brand new package and recreating all the tasks from scratch, swapping between a VB and C~ version of the script task and as yet none have resolved the issue. The only viable workaround at the moment is using package configurations for the connections which are working.
MCITP SQL 2005, MCSA SQL 2012
- RTaylor2208 - Wednesday, November 21, 2018 7:11 AM
where does it know where to connect and retrieve the correct connections strings? that may be where the problem is.
Edit: To ensure that you are indeed going to the correct place, and retrieving the correct configurations, on the script task that retrieves them fire a log info with both the connection to the config db and the retrieved connection strings (remove password from them if applicable) - that way you can look at the ssis log output and see if the data is what you expected - if it is then you have another issue, if it isn't then you know what the issue is. -
The only viable workaround at the moment is using package configurations for the connections which are working.
Deploying the packages to SSISDB and using SSISDB environment references / variables would also fix this issue, I am sure.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - frederico_fonseca - Wednesday, November 21, 2018 7:19 AM
Good question and something I forgot to mention, the package takes a variable for the server name that stores the DB with that table. At execution time there is an expression on the connection manager for that configuration database that does sucessfully update the ServerName property, the DB name is static as this does not change from server to server.
I have confirmed this functionality is working by running a trace during package execution and can see that the script task is indeed running the query successfully to retrieve the connection strings for the other connections.
MCITP SQL 2005, MCSA SQL 2012
- Phil Parkin - Wednesday, November 21, 2018 7:23 AM
I would also agree with this, for the moment though we have over 1600 packages that need to be migrated from working with SQL 2008 R2 to SQL 2014 and the preference is to stick with the package deployment model and only upgrade the packages to work with the latest version rather than do any sort of redevelopment.
MCITP SQL 2005, MCSA SQL 2012
-
One other nugget of information which strikes me as odd, if we import the package into SSIS the script task is populated with our custom code to set the connections, if we then export the same package and reopen it in SSDT the script task has reverted to the default code with all out code missing. The packages are not encrypted as non sensitive data is stored within so I don't think thats the source of the issue. But it does make me think that when the package is executing in SSIS its essentially running an empty task hence no errors.
The script task is definetly populated as its showing as correct when we pull it out of our source control system.
MCITP SQL 2005, MCSA SQL 2012
-
Typically a colleague has just found the issue after a few hours looking at this issue. The project properties for the solution had the "TargetServerVersion" field set to "SQL server 2016", once changed to SQL 2014 and the package rebuilt and deployed to SSIS it works as expected 🙂
The thing to change is below:
MCITP SQL 2005, MCSA SQL 2012
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply