none
How to embed SSIS package password in a SQL Job Agent

    Question

  • Hi,

    I'm having a problem with saving a password in a SSIS package that is accessing an Oracle database as an external data source.  I experienced the problem that many people have had with the SSIS package not saving the Oracle password as expected.  I successfully saved the Oracle datasource password (Password #1) using the "Encrypt sensitive with password" security setting and then setting a separate/new password (Password #2) for the SSIS package.  I've imported this package into my Integration Services installation, and I can get it to run successfully by manually entering Password #2 when I open the package.

    Now I want to pull the SSIS package into a SQL Job Agent, along with some other steps, to run on an automatic schedule.  My question -- how/where do I embed Password #2 (the one for the SSIS package) in the settings for the SQL Job Agent step that I use to run the SSIS package?  The SQL Job Agent step settings box prompts me for Password #2 before it will let me edit settings for the package, but it doesn't save that password.  So when I actually run the whole job, the SSIS package always fails.

    Thanks much,

    Bill J.

    Tuesday, June 14, 2011 4:14 PM

Answers

All replies

  • Hi Bill,

    Can you please post the error in the job? When the password provided is correct in the sql agent job, the step should be successful(SQL server 2008). There is no separate setting for the password in sql agent step. I also guess this is related to http://support.microsoft.com/kb/918760


    http://deepaksqlmsbusinessintelligence.blogspot.com/ Happy to help! Thanks. Regards and good Wishes, Deepak.
    Tuesday, June 14, 2011 4:35 PM
  • Modify the SQL Agent job to use the /DECRYPT option with your password in the command line, i.e. /DECRYPT "Password#2" 

    Wednesday, June 15, 2011 3:14 AM
  • I would definately consider using a proxy account with Active Directory if that is available. It ultimately makes development easier because you set all your datasources to use integrated security - and there is no storing of passwords in the package.

    http://msdn.microsoft.com/en-us/library/ms189064.aspx

    • Marked as answer by Bill in MI Wednesday, June 15, 2011 3:23 PM
    Wednesday, June 15, 2011 3:26 AM
  • Hi -- Here is the entire log for the Job Agent.  There are two steps involved: Step 1 drops the existing RAW.PERRANK table so that it can be replaced.  Step 2 calls the SSIS package to copy the table PERRANK from the Oracle database into a new table in SQLServer called RAW.PERRANK.  I know the error message refers to "Oracle client and networking components were not found" but the package runs fine when I do it in Integration Services and manually enter Password #2.

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

    06/15/2011 08:58:56,Reload PERRANK,Error,0,EASTERN-CD3494B,Reload PERRANK,(Job outcome),,The job failed.  The Job was invoked by User EASTERN-CD3494B\wjones20.  The last step to run was step 2 (Copy PERRANK from BERP).  The job was requested to start at step 1 (Delete old PERRANK).,00:00:02,0,0,,,,0

    06/15/2011 08:58:56,Reload PERRANK,Error,2,EASTERN-CD3494B,Reload PERRANK,Copy PERRANK from BERP,,Executed as user: NT AUTHORITY\NETWORK SERVICE. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  8:58:57 AM  Error: 2011-06-15 08:58:58.39     Code: 0xC0202009     Source: Copy PERRANK to new table RAW PERRANK Connection manager "SourceConnectionOLEDB"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80004005  Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.    Provider is unable to function until these components are installed.".  End Error  Error: 2011-06-15 08:58:58.42     Code: 0xC020801C     Source: Data Flow Task 1 Source - PERRANK [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2011-06-15 08:58:58.43     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: component "Source - PERRANK" (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2011-06-15 08:58:58.45     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-06-15 08:58:58.45     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  8:58:57 AM  Finished: 8:58:58 AM  Elapsed:  1.328 seconds.  The package execution failed.  The step failed.,00:00:02,0,0,,,,0

    06/15/2011 08:58:56,Reload PERRANK,Success,1,EASTERN-CD3494B,Reload PERRANK,Delete old PERRANK,,Executed as user: NT AUTHORITY\NETWORK SERVICE. The step succeeded.,00:00:00,0,0,,,,0


    Bill Jones Director, ITIM Eastern Michigan University
    Wednesday, June 15, 2011 1:04 PM
  • Han,

    I tried entering the password in the command line at /DECRYPT  as you suggested.  When I closed the job agent and ran it, it didn't work.  I also tried adding the password, closing the job agent and then reopening it to see if it saved my edit to the command line--it doesn't appear to be saving it.  So, I don't think the password is still there in the command line when I close the job agent editor and then run the job.

    Thanks,

    Bill


    Bill Jones Director, ITIM Eastern Michigan University
    Wednesday, June 15, 2011 1:08 PM
  • Hi,

    This worked!  I viewed a video at http://msdn.microsoft.com/en-us/library/dd440760(v=sql.100).aspx that went through all the solutions and tried every one of them.  And none of them worked.  I kept thinking about the error message that said the agent couldn't see the Oracle client and wondered if it was role permissions problem.  Turns out that it was--but none of the other solutions worked because of it, so it seemed like everything was broken.  Phew. 

    Thanks!

    Bill


    Bill Jones Director, ITIM Eastern Michigan University
    Wednesday, June 15, 2011 3:25 PM
  • How did you resolve the roles permission problem, if I still may ask after all this time?

    Susie


    susiew32

    Friday, July 27, 2012 5:28 PM
  • Susie,

    Phew, it has been a long time since I worked on this.  I saved all the steps I developed for this work in a job aid, and I'm pasting in a generic version of that process--hope it works for you.  The video I mentioned in the posts is very worth watching to get grounded in the process.  Good luck, Bill

    SSIS Package for copying tables from Oracle DB

    1)    (If not already done) On database server in Security create a Credential
        o   Name – “YourUser#1”
        o    Identity – Administrator
        o    Password – “yourpassword#1”

    2)    (If not already done) In SQL Server Agent create a Proxy under “SSIS Package Execution”
        o    Proxy name – “YourSSISProxy#1”
        o    Credential Name – “YourUser#1” (it’s the Credential you just created in the database Security area)
        o    Active to the following subsystems – SQL Server Integration Services Package

    3)    Create an SSIS Package using the Import Data tool in SQL Server.
        o    Go through the wizard using the normal settings to copy the external data
        o    Check “Save SSIS Package” with “SQL Server” as destination (not “File system”). You can do this alongside the “Run Immediately” setting
        o    Change Package Protection Level to “Encrypt sensitive data with password” and the password “yourpassword#1” (The password you created with the Security Credential)
        o    Run your import.  The package will appear in Integration Services (separate connection from Database Engine inside SQL Server Management Studio) under StoredPackages/MSDB.
        o    Right-click on the package name and open Package roles.  Change the Reader Role to “db_ssisadmin”

    4)    Create a Job Agent with a step to use the SSIS package.  
        o    Type -- SQL Server Integration Services Package
        o    Run as – “YourSSISProxy#1”
        o    Package Source – SSIS Package Store
        o    Server – whatever server we’re on
        o    Log on – Use Windows Authentication
        o    Package – select from list
        o    Enter “yourpassword#1” whenever required

    5)    Run it.

    Resources
    Troubleshooting: SSIS Package Execution Using SQL Server Agent (SQL Server Video)
    http://msdn.microsoft.com/en-us/library/dd440760(v=sql.100).aspx

    Bill Jones wjones20@emich.edu

    Monday, July 30, 2012 12:47 PM
  • Thanks, Bill.  This is a really big help, when trying to get this all to work.

    I actually got my job to run.  I made sure that the user set as "run as" had access to all the resouces necessary.  But  I think in the end for me it was that I was using a 32-bit connector for mysql, and so under Execution Options in my job step,  I had to set the 'Use 32bit runtime'.

    Susie


    susiew32

    Monday, July 30, 2012 1:29 PM
  • Thanks for this Bill, it really helped me.
    Tuesday, August 14, 2012 9:54 AM
  • Take a look at this:

    http://thomaskrehbiel.com/post/1260-the_dtexec_decrypt_option

    The key point is in this sentence:

    Add "/DECRYPT password" to the DTEXEC command line parameters and the errors go away.

    dtexec /FILE "MyPackage.dtsx" /DECRYPT password /CONFIGFILE "MyPackage.dtsConfig"

    Monday, January 07, 2013 2:27 PM
  •     o    Check “Save SSIS Package” with “SQL Server” as destination (not “File system”). You can do this alongside the “Run Immediately” setting
        o    Change Package Protection Level to “Encrypt sensitive data with password” and the password “yourpassword#1” (The password you created with the Security Credential)

    Actually you don't need any of the other stuff just the two steps above. Proxy accounts and credentials are for different things, you shouldn't mix it up with the "with password" package encryption type.


    Monday, January 07, 2013 2:38 PM