locked
SSIS and SQL Agent RRS feed

  • Question

  • I tried doing following and received error I am tryin to extract data from a SQL 2000(other machine) to SQL 2005(my machine) I made one SSIS package on SQL 2005. package works fine when i execute it, problem starts when i try to automate using a job in sql agent, I created a new job. and when i schedule it...the job fails with following error.. i have deployed package on my machine,, do i need to deploy it on source sql server,,, error is as follows.... Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Code: 0xC0202009 Source: Package Connection manager "servername.SSISpackage name.username" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D... The package execution fa... The step failed. C:\Program Files\Microsoft SQL Server\90\DTS\Packages\PackageName
    Friday, June 5, 2009 9:54 AM

Answers

  • resolved the issue..

    check followin ................................



    In the SQL Server 2005, after you create a SSIS package ( DTS), you want to create a job and schedule to run it. You will get the error to prevent you to run the job. What is the problem?

     

    Here is why: SQL Server 2005 is quite different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, you need to go through the security layer in order to run the job.

     

    The logic is like this:

          The job executor account needs the roles of sysadmin,

      SQLAgentUserRole, SQLAgentReaderRole,  SQLAgentOperatorRole

          The job needs to be run under Proxy account

          The job executor account is better to be used to create SSIS package and so you are sure the job executor account has the right to run this SSIS package.

     

    The following steps can be followed to get the job done.

    The work environment is MS SQL Server Management Studio and you log in as sa.

     

    I. Create job executor account

    Highlight Security->New Login, say to make login as devlogin, type your password, default database can be your target database.

    Server roles: check �sysadmin

    User mapping: your target database

    Msdb database: you make sure to include SQLAgentUserRole, SQLAgentReaderRole,  SQLAgentOperatorRole

    Then click OK

     

    II. Create SQL proxy account and associate proxy account with job executor account

    Here is the code and run it the query window.

     

    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'

     

    III. Create SSIS package

    In MS SQL Server Business Intelligence Development Studio, you use job executor account devlogin to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business Intelligence Development Studio. Compile/build this package.

     

    IV. Create the job, schedule the job and run the job

    In SQL Server Management Studio, highlight SQL Server Agent -> Start. Highlight Job ->New Job�, name it , myJob.

    Under Steps, New Step, name it, Step1,

    Type: SQL Server Integration Service Package

    Run as: myProxy

    Package source: File System

    Browse to select your package file xxx.dtsx

    Click Ok

    Schedule your job and enable it

     

    Now you can run your job

    • Marked as answer by CRMDevlpr Friday, June 5, 2009 11:26 AM
    Friday, June 5, 2009 11:26 AM

All replies

  • resolved the issue..

    check followin ................................



    In the SQL Server 2005, after you create a SSIS package ( DTS), you want to create a job and schedule to run it. You will get the error to prevent you to run the job. What is the problem?

     

    Here is why: SQL Server 2005 is quite different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, you need to go through the security layer in order to run the job.

     

    The logic is like this:

          The job executor account needs the roles of sysadmin,

      SQLAgentUserRole, SQLAgentReaderRole,  SQLAgentOperatorRole

          The job needs to be run under Proxy account

          The job executor account is better to be used to create SSIS package and so you are sure the job executor account has the right to run this SSIS package.

     

    The following steps can be followed to get the job done.

    The work environment is MS SQL Server Management Studio and you log in as sa.

     

    I. Create job executor account

    Highlight Security->New Login, say to make login as devlogin, type your password, default database can be your target database.

    Server roles: check �sysadmin

    User mapping: your target database

    Msdb database: you make sure to include SQLAgentUserRole, SQLAgentReaderRole,  SQLAgentOperatorRole

    Then click OK

     

    II. Create SQL proxy account and associate proxy account with job executor account

    Here is the code and run it the query window.

     

    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'

     

    III. Create SSIS package

    In MS SQL Server Business Intelligence Development Studio, you use job executor account devlogin to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business Intelligence Development Studio. Compile/build this package.

     

    IV. Create the job, schedule the job and run the job

    In SQL Server Management Studio, highlight SQL Server Agent -> Start. Highlight Job ->New Job�, name it , myJob.

    Under Steps, New Step, name it, Step1,

    Type: SQL Server Integration Service Package

    Run as: myProxy

    Package source: File System

    Browse to select your package file xxx.dtsx

    Click Ok

    Schedule your job and enable it

     

    Now you can run your job

    • Marked as answer by CRMDevlpr Friday, June 5, 2009 11:26 AM
    Friday, June 5, 2009 11:26 AM
  • resolved the issue..

    check followin ................................



    In the SQL Server 2005, after you create a SSIS package ( DTS), you want to create a job and schedule to run it. You will get the error to prevent you to run the job. What is the problem?

     

    Here is why: SQL Server 2005 is quite different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, you need to go through the security layer in order to run the job.

     

    The logic is like this:

          The job executor account needs the roles of sysadmin,

     

          The job needs to be run under Proxy account

          The job executor account is better to be used to create SSIS package and so you are sure the job executor account has the right to run this SSIS package.

     

    The following steps can be followed to get the job done.

    The work environment is MS SQL Server Management Studio and you log in as sa.

     

    I. Create job executor account

    Highlight Security->New Login, say to make login as devlogin, type your password, default database can be your target database.

    Server roles: check �sysadmin

    User mapping: your target database

    Msdb database: you make sure to include SQLAgentUserRole, SQLAgentReaderRole,  SQLAgentOperatorRole

     

    Then click OK

     

    II. Create SQL proxy account and associate proxy account with job executor account

    Here is the code and run it the query window.

     

    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'

     

    III. Create SSIS package

    In MS SQL Server Business Intelligence Development Studio, you use job executor account devlogin to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business Intelligence Development Studio. Compile/build this package.

     

    IV. Create the job, schedule the job and run the job

    In SQL Server Management Studio, highlight SQL Server Agent -> Start. Highlight Job ->New Job�, name it , myJob.

    Under Steps, New Step, name it, Step1,

    Type: SQL Server Integration Service Package

    Run as: myProxy

    Package source: File System

    Browse to select your package file xxx.dtsx

    Click Ok

    Schedule your job and enable it

     

    Now you can run your job

     

      SQLAgentUserRole, SQLAgentReaderRole,  SQLAgentOperatorRole


    Man You are The King :)

    Thank you i've been searching for Hours for that.

     

    Monday, July 5, 2010 9:01 PM
  • Proxy is an option for authentication.

    Here is my observation if SSIS file move task not moving files by using Agent Job but working inside BIDS.

    If the path is Network Drive, you have to give absolute UNC path rather than using shared drives e.g. Z:\FTP\etc

    UNC path \\ABCINT01\d$\FTP\.......

    Trust me, If you are using Domain\Administrator or any Domain User that has rights on all network servers, it will work.

    I have domain user ABCCluster\Administrator having same password on all Domain authenticated database, ftp, web servers, and agent job is started/authenticated using same account, there will be no problem.

    Thanks

    Qaiser

    Tuesday, January 21, 2014 6:27 AM