1

I have several developments with the following workflow

OBDC(Avaya - Informix Table) ---> SSIS(ETL) Connection String ,Query ---> SQL Server 2014 Table

For the ssis package i use connection strings , the policies on my company makes me reset my Avaya password every 28 days , and i've got up to 50 packages , the packages run on my machine with scheduled tasks and command line , since i don't have privileges on my servers for programming or exporting this packages and develop the scheduled task on them , im only able to run the packages or mounting them on MSDB , however the package with the informix driver used for the ETL process won't work once its uploaded on the server , because it needs the driver and even if it's installed on the server it doesn´t seem to recognize the driver though the process (I'm also a user without full privileges on my machine or server , im an advanced user only ).

I tried to use parameters and variables in order to get the connection string , user and mostly important , the password (delayed login, work offline , user and password from an SQL Server Table configs don´t work ), Since the connection manager doesn´t seem to work with these variables (updated by parameters ) , i also tried configuration file in order to get the password from a safe source (by editing the xml on de dtsx package ) and changing to deployment . however none of them worked . I wonder if there is a workaround with scripting that works with avaya tables .

Is there any detailed guide or method regarding how to leave my packages as they are (Once i find the way to get the password from an automatic and reliable source , such as a file or sql table , xml , flatfile , etc etc ), and make a simple update so the password is updated on all of my packages at once ? Updating every package every 28 days drives me nuts and doesn't seem like a developer solution(yeah i know how i can be a developer with my hands all tied , but i try to keep the style at least).

Thank you all.

Ernesto Garcia
  • 31
  • 3
  • You must find out 1 thing first - has SQL Server integration services been installed on the server which executes the packages? – Wendy Jun 19 '17 at 14:36
  • Thanks Wendy , indeed yes , i've got limited permissions , in this case im able to load ssis packages on the msdb folder with my user – Ernesto Garcia Jun 19 '17 at 21:25
  • Do the packages run on your machine successfully, with all your tries using parameters, configuration file, etc? – Wendy Jun 20 '17 at 11:59
  • Thanks again , no , the parameters doesn't seem to work with odbc connections , it always prompts for error , if i use them with ole db no errors , configuration file also , but whenever ODBC is involved i can't run the file succesfully , the odbc connections appears to have problems with variables and parameters , config files , i wonder if it is because of informix – Ernesto Garcia Jun 20 '17 at 15:20
  • If you contruct connection string correctly, it should work. Anyway, you must test your packages on your own machine successfully, then you can deploy to production. By the way, nowadays, most packages are deployed to Integration Services Catalogs, not MSDB. All the configurations are in the SSISDB. – Wendy Jun 20 '17 at 15:29

0 Answers0