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.

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/918760http://deepaksqlmsbusinessintelligence.blogspot.com/ Happy to help! Thanks. Regards and good Wishes, Deepak.

Modify the SQL Agent job to use the /DECRYPT option with your password in the command line, i.e. /DECRYPT "Password#2"

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

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

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, BillBill Jones Director, ITIM Eastern Michigan University

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! BillBill Jones Director, ITIM Eastern Michigan University

How did you resolve the roles permission problem, if I still may ask after all this time? Susiesusiew32

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 (its 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 were 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).aspxBill Jones wjones20@emich.edu

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'. Susiesusiew32