SSIS Deploy Connection Properties for ODBC

1
votes

I have a Postgres source on one network, a SQL Server target on another, and I'm developing a data-flow task on my desktop that I want to deploy to the SQL Server SSIS Catalog.

I've managed to use the .NET Framework ODBC connection by building a new DSN using a connection string, saving it locally as a DSN file, providing a username / password and using that object as the source connection manager. That works fine.

Now I'm trying to deploy the project / package to the SSIS Catalog on my Production server, and I'm not sure what exactly I need to do to establish the same source DSN.

Do I parameterise the connection string and pass a hard-coded user/pass? Do I re-build a matching DSN on the server with the same name / path? Do I create a system DSN?

Can anyone give a step by step on best practice for managing connections / drivers / passwords in the SSIS Deployment Model?

Thank you.

1
sql-serverpostgresqlssis
Where will the SSIS package run? On the same server as SSISDB or another? – user1443098
On the same sql server – Eyal Zinder

1 Answers

1
votes

There are a few options: 1. Define an ODBC connection on the server running the SSIS package, just like the one on your desktop, and save the password with it. 2. Define a linked server in SQL Server and change your package to use it. 3. Use SSISDB Environments to set the connection strings at run time. 4. When you run DTEXEC, specify the connection strings on the command line.

...and probably a few more.