July 8, 2014 at 3:32 am
Hi,
What is the best way to give SQL Agent permission to run a job that accesses an Oracle db?
I have an SSIS package that pulls data from a table back from an Oracle 11g db. The package works fine in Visual Studio 2010 and also when executed from the MSDB Package store.
When I try to run the package as a SQL Agent Job it fails though. Here's an excerpt from the error message:
Code: 0xC0202009 Source: Populate_Products Connection manager "CISLIVE.user 1" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied". End Error
A bit of googling told me that I needed to edit the command line in the Agent job with the Oracle db password that the SSIS connection manager uses. I've added that like this:
@command=N'/DTS "\"\MSDB\FolderName\1_Overall_Populate_Tables\"" /SERVER "\".\"" /DECRYPT mypassword /CHECKPOINTING OFF /REPORTING E'
But I still see the "ORA-01005: null password given; logon denied" error.
Does anyone have any experience of tackling this and how to resolve it all?
many thanks
Lins
July 8, 2014 at 5:26 am
You need to store the connection information into a package configuration.
Understanding Integration Services Package Configurations
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 8, 2014 at 7:38 am
Koen Verbeeck (7/8/2014)
Hi, Thank you for the response. I've read the article and had a look at creating a package configurations. The package in question will execute a number of 'Execute Package Tasks', each one populating a different db table.
Which is the best package configuration type to use?
I think I need real newbie step by step help here I'm afraid.
thanks
lins
July 8, 2014 at 8:03 pm
Create a SSIS Package that store the Oracle datasource and Oracle login credentials e.g. Use a Configuration File to store these details
Deploy Package to SQL Server and Create a SQL Agent job to execute the package with Type = SQL Server Integration Services Package
You'll also need Oracle Client software on the SQL Server.
July 9, 2014 at 1:12 am
lindsayscott23 (7/8/2014)
Koen Verbeeck (7/8/2014)
You need to store the connection information into a package configuration.Hi, Thank you for the response. I've read the article and had a look at creating a package configurations. The package in question will execute a number of 'Execute Package Tasks', each one populating a different db table.
Which is the best package configuration type to use?
I think I need real newbie step by step help here I'm afraid.
thanks
lins
Personally I prefer configuration tables. This means you also need a connection string in your package that points the package to the database where it can find the configuration table (called an indirect configuration). Each package should go to the configuration table and search for connection manager properties.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 9, 2014 at 2:44 am
Personnaly I think using SSIS 2012 (SQL Server Data Tools 2012 + Project deployment + SSISDB) is the best solution.
You can modulate your environnement with variables.
July 9, 2014 at 9:52 am
Hi all,
Thanks for your suggestions, they've been really helpful.
I've been making some progress, but SQL Agent still won't run the job - albeit with a different error message now.
I successfully created a configuration table and added the oracle db password to the connection string in the ConfiguredValue field. It looks like this:
Data Source=DATABASENAME;User ID=userid;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Password=mypassword
The packages run fine in VS2010 and from the SSIS package store.
When I run the SQLAgent job it fails and the error message is now geared around it being unable to decrypt the key:
Error: 2014-07-09 16:41:37.53
Code: 0xC0014060
Source: {2C0536A5-36F1-4356-8A2B-E60474CDC24D}
Description: Failed to decrypt a package that is encrypted with a user key. You may not be the user who encrypted this package<c/> or you are not using the same machine that was used to save the package.
The command line in the SQl agent Job is as follows:
@command=N'/DTS "\"\MSDB\foldername\1_Overall_Populate_Tables\"" /SERVER "\".\""/DECRYPT mypassword /CHECKPOINTING OFF /REPORTING E'
Am I putting the DECRYPT in the wrong part of the command line (I have experimented with it in other positions without luck)?
When I create the Package Configuration do I need to explicitly tell the package to use that, or does it use them if they are available?
Any ideas what I'm doing wrong here at all? My sense is that there's a middle piece to the puzzle that I'm missing.
Really appreciate any more help / advice you can give
cheers
Lins
July 9, 2014 at 10:57 am
Change the protection level of the package from EncryptSensitive to DontSaveSensitive.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 10, 2014 at 2:33 am
Hi Koen,
That works! Thanks loads. After a few days of wrestling with that it's great to see that agent job status finally say 'Success'.
I spoke to my boss about this solution and he has recommended that I opt for an XML based approach, so I'm going to investigate that side of things now.
Cheers for your help everyone
Lins
July 10, 2014 at 2:35 am
Glad you got it working!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply