?
Solved

SSIS Data Flow Task Error

Posted on 2014-04-28
11
Medium Priority
?
7,744 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

718 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