Solved

SSIS Data Flow Task Error

Posted on 2014-04-28
11
6,226 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now