Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS Data Flow Task Error

Posted on 2014-04-28
11
Medium Priority
?
8,200 Views
Last Modified: 2016-02-10
I am trying to copy one table from an Access database to SQL Server using SSIS. I’m running SQL Server 2008 R2. I get data flow task errors.

Here are the steps that I performed:
1. Pasted the code shown below into the OLE DB Destination Editor (Table or view - fast load).
2. Click Mappings in the OLE DB Destination Editor to go to the Mappings page.
3. Clicked OK. When I executed the sequence container named "Create Tables - Attempt 2," four errors occurred (see screen shots).

The statement:
CREATE TABLE [dbo].[tblDurations](
[DurationID] [int] NOT NULL,
[Duration] [tinyint] NULL
)

What is causing the errors?

Code-1.jpgData-Flow-Task.jpgDuration-Detail.jpgError-List.jpgmappings.jpgOledb-Dest-Editor.jpgSequence-Container.jpg

SSIS ERRORS
Error 1 Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80040E37 Description: “Invalid object name ‘tblDurations’.”. Test2_041514.dtsx 0 0
--------------
Error 2 Validation error. Data Flow Task: Data Flow Task: Failed to open a fastload rowset for “[tblDurations]”. Check that the object exists in the database. Test2_041514.dtsx 0 0
-----------------
Error 3 Validation error. Data Flow Task Destination - tblDurations 1 [108]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80040E37 Description: “Invalid object name ‘tblDurations’.”. Test2_041514.dtsx 0 0
---------------------
Error 4 Validation error. Data Flow Task Destination - tblDurations 1 [108]: Failed to open a fastload rowset for “[tblDurations]”. Check that the object exists in the database. Test2_041514.dtsx 0 0
0
Comment
Question by:Mark01
  • 6
  • 5
11 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40029141
It looks like the data flow task is failing because it cannot find the destination tasks, and looking at the snapshot of the components in the package it seems there are no  precedence links between the tasks.
Please make sure the tasks/components are linked properly to ensure for instance the destination table is created before the data flow task starts.
It can also be the case that you may need to set "Delay Validation" attribute to false on the pertinent tasks and connections.
0
 

Author Comment

by:Mark01
ID: 40029880
The screenshot shows the green and red arrows. I dragged the green arrow from the source to the destination to create an On Success Precedence Constraint. Is there any other way to make sure that the destination table is created before the data flow task starts?
Green and Red Arrows
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40030039
Just make sure the line is actually connecting the two tasks - like the attached snapshot.
That should do the trick. The green line determines the flow when the task succeeds and the red one determines the flow when it fails. Only one can go to the same destination.

You can see the actual order of execution when you run the package in debug mode.
SSIS-Flow.png
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Mark01
ID: 40030502
The screenshot above (ID: 28421600) with the caption "Data Flow Task" above shows the actual connection between the two tasks.

The attached screenshot shows the order of execution that is displayed in the Progress tab.

Here is the text of the errors:

(1)
[Source - tblDurations 1 1 [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

(2)
[SSIS.Pipeline] Error: component "Source - tblDurations 1 1" (1) failed validation and returned error code 0xC020801C.

(3)
[SSIS.Pipeline] Error: One or more component failed validation.

(4)
Error: There were errors during task validation.
Order of Execution
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40030912
Have you tried to set "delay validation" to true for the pertinent task/connection? The default is false, so it tries to validate the the destination table and the mappings before the table is even created.
0
 

Author Comment

by:Mark01
ID: 40031037
I set "delay validation" to true for the sequence container and then executed the sequence container. I get the same errors as in ID: 40030502.
0
 

Author Comment

by:Mark01
ID: 40032710
Is it possible that the errors are caused by Win. 7 security settings?  The user has to have sufficient permissions.
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40033469
Unlikely! If it was a permission issue it would be something like cannot create table or cannot acquire connection. This is a validation error, which sugeest that at the destinationtable doesn't exist. Could you please check the precedence links between your compoenents/tasks inside the sequence container?

You can also try disabling the all tasks and re-enable them one by one to eliminate the culprit... for instance disable the data flow and just execut the taks that creates the table.
0
 

Author Comment

by:Mark01
ID: 40033598
I don't think the sequence container has any precedence links. A screen shot of the sequence container is in ID: 28421600, above, with a caption of Sequence Container. There is only one link inside the Data Flow Task. You can view a screenshot of  the Data Flow Task in ID: 28421600, above.

I disabled all of the other sequence containers. I don't know how to disable the OLE DB Source for the Access database to only test the task that creates the table.

I am working with an Access 2010 database and the database file is in the Access 2010 format.

I created a new solution, project and package to test the OLE DB Source for the Access database. The package contains only a single Data Flow Task.

I get the errors listed below. They look like the same errors that are occurring with the original package containing multiple sequence containers. Do you agree that it appears that there is an error with the Access database connection?

[Source - tblDurations 1 1 1 [33]] Error: SSIS Error

Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  

The AcquireConnection method call to the connection manager "sample_040214" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.


[SSIS.Pipeline] Error: component "Source - tblDurations 1 1 1" (33) failed validation and returned error code 0xC020801C.
0
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 2000 total points
ID: 40033908
Ah that's a different error. Assuming that the Access driver has already been installed on the server you're running the package on, that means that you have to change the execution mode of you package to 32 bit. Right click on the project icon in solution explorer, and open up the properties, select "Configuration Properties" > "Debugging" and set Run64BitRunTime to False. That takes care of debugging the package in 32 bit. When you're done with development and deploying of the package to live, if the live server is also 64bit, you need to maker sure to set the package to run in 32bit mode.
0
 

Author Comment

by:Mark01
ID: 40034879
I am only working on a development computer; no server is involved. Changing the execution mode of the package to 32 bit may have solved the problem. The data did transfer with some errors. The attached screenshots show the errors.

Here are the errors and warning:

Order of Execution Warning:

[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.


Errors:

Error      1      Validation error. Data Flow Task: Data Flow Task: A connection manager has not been assigned to the runtime connection "OleDbConnection" (9).        Test2_041514.dtsx      0      0      



Error      2      Validation error. Data Flow Task OLE DB Destination [1]: A connection manager has not been assigned to the runtime connection "OleDbConnection" (9).        Test2_041514.dtsx      0      0      



Error      3      Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E37  Description: "Invalid object name 'tblDurations'.".        Test2_041514.dtsx      0      0      



Error      4      Validation error. Data Flow Task: Data Flow Task: Failed to open a fastload rowset for "[tblDurations]". Check that the object exists in the database.        Test2_041514.dtsx      0      0      



Error      5      Validation error. Data Flow Task Destination - tblDurations 1 [108]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E37  Description: "Invalid object name 'tblDurations'.".        Test2_041514.dtsx      0      0      



Error      6      Validation error. Data Flow Task Destination - tblDurations 1 [108]: Failed to open a fastload rowset for "[tblDurations]". Check that the object exists in the database.        Test2_041514.dtsx      0      0      


Thank you, ProjectChampion.
Container Execution SuccessErrorsOrder of Execution
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What we learned in Webroot's webinar on multi-vector protection.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question