March 26, 2014 at 3:18 am
Hi All
I am really struggling to find the source of this issue. I am using SQL Server 2012 with Visual Studio 2010. I have an SSIS package that is taking data from a SQL table and loading it into a csv file. I am using an expression for the file name to add the date and time. All works perfectly in Visual Studio.
However when I configure a SQL server agent job to run this package I get the below error. I have done a lot of research and tried many of the solutions but nothing worked.
Any or all help is much appreciated.
Many thanks
Trish
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3401.0 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 05:57:37
Error: 2014-03-26 05:57:38.13
Code: 0xC001000E
Source: GPSales_ReturnsData
Description: The connection "{EFC6889A-D312-4A9E-B251-877C7A67B8DF}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Error: 2014-03-26 05:57:38.13
Code: 0xC004800B
Source: GP_SalesData SSIS.Pipeline
Description: Cannot find the connection manager with ID "{EFC6889A-D312-4A9E-B251-877C7A67B8DF}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "vGPSalesOrders.Connections[OleDbConnection]" in the connection manager collection of "vGPSalesOrders". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.
End Error
Error: 2014-03-26 05:57:38.13
Code: 0xC0047017
Source: GP_SalesData SSIS.Pipeline
Description: vGPSalesOrders failed validation and returned error code 0xC004800B.
End Error
Error: 2014-03-26 05:57:38.13
Code: 0xC004700C
Source: GP_SalesData SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2014-03-26 05:57:38.13
Code: 0xC0024107
Source: GP_SalesData
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 05:57:37
Finished: 05:57:38
Elapsed: 0.343 seconds
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3401.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 06:15:51
Error: 2014-03-26 06:15:52.48
Code: 0xC020200E
Source: GP_SalesData Flat File Destination [2]
Description: Cannot open the datafile "L:\Information Technology\Business Intelligence\BI Project\GPFiles\GPSalesOrdersReturns_20140326_0615.csv".
End Error
Error: 2014-03-26 06:15:52.48
Code: 0xC004701A
Source: GP_SalesData SSIS.Pipeline
Description: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 06:15:51
Finished: 06:15:52
Elapsed: 0.625 seconds
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3401.0 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 06:56:48
Error: 2014-03-26 06:56:48.54
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [2]
Description: Cannot open the datafile "L:\Information Technology\Business Intelligence\BI Project\GPFiles\GPSalesOrdersReturns_20140326_0656.csv".
End Error
Error: 2014-03-26 06:56:48.54
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 06:56:48
Finished: 06:56:48
Elapsed: 0.406 seconds
March 26, 2014 at 3:55 am
There seem to be 2 issues:
* an ole db connection manager is not found. It surprises me that this error is not present in BIDS.
* the file "L:\Information Technology\Business Intelligence\BI Project\GPFiles\GPSalesOrdersReturns_20140326_0656.csv" cannot be opened.
Either the L drive doesn't exist on the server, or the file doesn't exist or the SQL Server Agent account doesn't have permissions to open the file.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 26, 2014 at 4:23 am
There seem to be 2 issues:
* an ole db connection manager is not found. It surprises me that this error is not present in BIDS.
* the file "L:\Information Technology\Business Intelligence\BI Project\GPFiles\GPSalesOrdersReturns_20140326_0656.csv" cannot be opened.
Either the L drive doesn't exist on the server, or the file doesn't exist or the SQL Server Agent account doesn't have permissions to open the file.
Thanks For your reply. in Bids he package runs without error. The L drive is a mapped network drive on the server could this be an issue?
How can I find out if the SQL server agent account has permissions?
Many thanks
March 26, 2014 at 4:26 am
The OLE DB connection manager doesn't just dissapear when you deploy the package to the server.
If you open up the source component, do you see the connection managers name?
To check the permissions: find out which account is used by SQL Server Agent, go to the folder and check which accounts hold which permissions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 26, 2014 at 4:35 am
IN the Data source properties of the job step both connections are visible the ole db and the csv so the job is seeing the connections. The SQL server agent is using the same user as the package in BIDS and they have permissions to the drive.
March 26, 2014 at 4:36 am
Could it have anything to do with package configurations?
March 26, 2014 at 4:37 am
tlally (3/26/2014)
IN the Data source properties of the job step both connections are visible the ole db and the csv so the job is seeing the connections. The SQL server agent is using the same user as the package in BIDS and they have permissions to the drive.
How do you "use" another user in BIDS? BIDS runs with your user account.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 26, 2014 at 4:38 am
tlally (3/26/2014)
Could it have anything to do with package configurations?
It might. If you change connections with those configurations.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 26, 2014 at 4:45 am
I dont have any package configurations I was wondering do I need to add them?
What other areas should I review based on the error I am getting?
March 26, 2014 at 4:50 am
The package should work without configurations.
For the missing connection: I would recreate the source component, test it and then deploy it back to the server.
For the file: I would check permissions. (write permissions in this case)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 26, 2014 at 6:15 am
Getting closer I did all tasks and now the only error is
Started: 12:13:37
Error: 2014-03-26 12:13:38.36
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [2]
Description: Cannot open the datafile "L:\Information Technology\Business Intelligence\BI Project\GPFiles\GPSalesOrdersReturns_20140326_1213.csv".
End Error
Error: 2014-03-26 12:13:38.36
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 12:13:37
Finished: 12:13:38
Elapsed: 0.625 seconds
March 26, 2014 at 7:14 am
If it works in BIDS, but doesn't with Agent, it is in 90% of the cases a permission issue.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 26, 2014 at 7:21 am
Koen Verbeeck (3/26/2014)
If it works in BIDS, but doesn't with Agent, it is in 90% of the cases a permission issue.
+1, it just a guess, just for the sake of curiosity is it a Network drive?
March 26, 2014 at 7:36 am
Yes it is a network drive.
March 26, 2014 at 7:40 am
Would a proxy account help?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply