September 29, 2009 at 10:44 am
In my SSIS package, I have a package variable called 'ETLConnectionString' which contains the following value: Data Source=localhost;Initial Catalog=ETL_Local;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;
On my local machine, I have a system Environment Variable called 'ETL_SQLServerOLEDBConnection' that contains the same connection string as mentioned in the first paragraph.
I have a package configuration that references the 'ETL_SQLServerOLEDBConnection' E.V., and points to the value of 'ETLConnectionString'
The 'ETLConnectionString' variable is used in the ConnectionString expression of a Native OLE DB\Microsoft OLE DB Provider for SQL Server connection manager called 'ETL'.
The package will execute from BIDS without a problem on my local machine.
On the machine that the package runs from for development testing, the E.V. has the same name of 'ETL_SQLServerOLEDBConnection', but the value is different: Data Source=DevServer;Initial Catalog=ETL;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;
Here is what I expect to happen once the package is on this server:
1. The E.V. package configuration will 'read' the connection string on DevServer from the ETL_SQLServerOLEDBConnection E.V.
2. The ETLConnectionString package variable will be set with the value of the ETL_SQLServerOLEDBConnection on DevServer.
3. The 'ETL' Connection Manager in the package will correctly read the ETLConnectionString package variable.
When the package runs on DevServer from a SQL Server Job, the following error appears after the Job runs: Error: 2009-09-28 13:17:12.55 Code: 0xC0202009 Source: CFISExtract Connection manager "ETL" 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 SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.". End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 1:16:55 PM Finished: 1:17:28 PM Elapsed: 32.438 seconds. The package executed successfully.
So, the package excutes successfully, but this error occurs. When I open the package on DevServer, the ETLConnectionString is correct. The connection string property of the 'ETL' connection manager is also correct. However, something is still wrong with the attempt to connect to the database.
I know that the connection string in the E.V. on DevServer is correct. What else could the problem be?
Thank you for your help.
CSDunn
September 29, 2009 at 2:23 pm
Can you run the package in BIDS on the dev server?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply