Running a SSIS Package with an ODBC connection poses specific problems
that force you to take extraordinary measures to run them in a job step. I am no expert here...what follows if my understanding after researching the problem. Posts to add clarity and alternate solutions would be appreciated.
These problems are summarized as follows:
1) ODBC connections typically require passwords to be saved with the connection.
Passwords are encrypted, typically with the user key (this is an option in the package,
but there is no option NOT to encrypt it).
The service cannot run this package without taking measures to make sure SQL
server impersonates the user who created the package.
2) ODBC runs on an older 32 bit technology.
Because of this, then standard 64 bit DTS utility that runs packages by default
in SQL server 2005 will not work (i.e.. You cannot select a "SQL Server
Integration Services Package" from the drop down menu).
Here is the way I managed to get this to work. This is one way this problem can be solved.
1). Create a SQL Server Proxy for the account that created the DTS Package.
SQL server uses proxies to communicate outside of SQL Server.
Under SQL Server Agent in Management Studio, right click on the proxy tab and add a new proxy.
I added one for our admin account. I created the package while logged in with this ID.
2). Add the package you created to SQL Server. I put my package in MSDB by connecting to SSIS,
expanding the "Stored Packages" folder, right-clicking on MSDB and selecting "Import Package".
I then browsed to my DTSX file and imported it.
My package had the default authentification method selected: EncryptSensitiveWithUserKey.
3). In the job, add a step to run an operating system command (CmdExe).
In the "Run As" section, select the proxy you created.
This will impersonate the package author. In the command text section,
use the following syntax: "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe"
/sq "Your Package Name" /SERVER ServerName
The DTExec.exe utility is the 32 bit SQL server DTS utility. The ODBC drivers I use need this to run.
Hope this helps someone.
Thanks,
Dan
that force you to take extraordinary measures to run them in a job step. I am no expert here...what follows if my understanding after researching the problem. Posts to add clarity and alternate solutions would be appreciated.
These problems are summarized as follows:
1) ODBC connections typically require passwords to be saved with the connection.
Passwords are encrypted, typically with the user key (this is an option in the package,
but there is no option NOT to encrypt it).
The service cannot run this package without taking measures to make sure SQL
server impersonates the user who created the package.
2) ODBC runs on an older 32 bit technology.
Because of this, then standard 64 bit DTS utility that runs packages by default
in SQL server 2005 will not work (i.e.. You cannot select a "SQL Server
Integration Services Package" from the drop down menu).
Here is the way I managed to get this to work. This is one way this problem can be solved.
1). Create a SQL Server Proxy for the account that created the DTS Package.
SQL server uses proxies to communicate outside of SQL Server.
Under SQL Server Agent in Management Studio, right click on the proxy tab and add a new proxy.
I added one for our admin account. I created the package while logged in with this ID.
2). Add the package you created to SQL Server. I put my package in MSDB by connecting to SSIS,
expanding the "Stored Packages" folder, right-clicking on MSDB and selecting "Import Package".
I then browsed to my DTSX file and imported it.
My package had the default authentification method selected: EncryptSensitiveWithUserKey.
3). In the job, add a step to run an operating system command (CmdExe).
In the "Run As" section, select the proxy you created.
This will impersonate the package author. In the command text section,
use the following syntax: "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe"
/sq "Your Package Name" /SERVER ServerName
The DTExec.exe utility is the 32 bit SQL server DTS utility. The ODBC drivers I use need this to run.
Hope this helps someone.
Thanks,
Dan