Solved

SSIS Data Flow Task Error

Posted on 2014-04-28
11
7,375 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
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…

623 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