December 17, 2018 at 10:00 am
Hey there ---
I have little SSIS experience but have used similar ETL tools so can work my around.
Previous (disgruntled frankly) employee left. His SSIS scripts all broke the next day.
I can figure edit the SSIS scripts in Visual Studio, and can see the errors. And fix them. However, when I "redeploy" the scripts to SQL server, I get all sorts of connection errors.
How SSIS manages connections is very confusing.
I have all the credentials for everything. It seems difficult to "save" these credentials. I even created brand new ones. I get the connection working locally, but once deployed, it always stops working. I don't know if this guy saved variables upon variables or "project wide/ solution wide/ package wide (I hate SSIS already)" credentials somewhere. Like, stuff that is over-riding the simple functioning connections.
Like, this is simple stuff here. I have all the server information, db names, instances, passwords (regular SQL login typically, not WinAuthent) -- and SSIS makes a dirt simple task needlessly complicated. If I need to encrypt this all in a text file somewhere, sure, just tell me that. Is there a guide to connection managers (not typing them in correctly, again, it works fine in Visual studio/ SSIS locally) --- it seems to never save especially when deploying. Hmm.
December 18, 2018 at 6:47 am
https://docs.microsoft.com/en-us/sql/integration-services/packages/deploy-integration-services-ssis-projects-and-packages?view=sql-server-2017
It looks like the "Deploy Projects to Integration Services Server" section of this page covers what you need, it mentions steps to keep connections in deployment.
January 3, 2019 at 6:34 am
HI Peter.
I agree that SSIS does tend to make simple things complicated but it also makes REALLY complicated things less complicated when you get really under the hood.
The short anwser is ...... it depends 🙂
Connection parameters can be stored within the package, in a configuration file, in environment variables, configured within the SQL Agent job or overriden on the command line if you are using DTUTIL EXEC, and if that is in a powershell or batch script then you have yet another level of abstraction.
I would first look to see how the packages are getting called as this will give you some indication of where the configuration is kept.
Further Actions
1) let your manager know so that HR know not to give references. Sabotaging your colleagues is not be tolerated and they should suffer the consequences
2) Documentation, Documentation, Documentation. Don't leave your successor in the invidious position
3) Source code control - Make sure all packages and configuration files are checked in
4) CI pipeline. You should be able to move a package from DEV to Test and Test to UAT/PROD without breaking the seal on it. All end points need to be in configuration parameters along with sufficient documentation that the DEV-OPS team can deploy in multiple environments.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply