Search code examples
mysqlsql-serverssisjobs

ssis package with proxy account in sql job

I made an SSIS package that exports data from Microsoft SQL Server and imports it into a .csv and mysql file. I start to use proxy account and it work, but only when user is logged. How make it works when user is logged off !? Thank you in advance. enter image description here enter image description here

Solution

  • I solved this problem. Firstly, created System DSN. link for this

    Does any connection manager in the package use ODBC DSN? If any ODBC DSN is used, please make sure the DSN type is System DSN rather than User DSN. The user DSN may not be accessible when the user that create the DSN logs off.

    Secondly, create new proxy and give him credentials link for this

    Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'