SSIS package will succeed with one table, but not all?

1
votes

Forgive my inexperience with this subject as this is the very first SSIS package I have created. The goal is to basically copy the schema/data from an Access DB into SQL Server. I use the import/export wizard in BIDS to create (there are 470 tables total). If I script it to do only one table, the job runs and executes the package, and is successful. When I try all tables I am given this error:

Executed as user: DOMAIN\USER. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:47:53 AM Error: 2012-07-16 11:48:03.65 Code: 0xC0202009 Source: Data Flow Task 1 Destination - Account [55] Description: 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 'dbo.Account'.". End Error Error: 2012-07-16 11:48:03.65 Code: 0xC0202040 Source: Data Flow Task 1 Destination - Account [55] Description: Failed to open a fastload rowset for "[dbo].[Account]". Check that the object exists in the database. End Error Error: 2012-07-16 11:48:03.65 Code: 0xC004706B Source: Data Flow Task 1 SSIS.Pipeline Description: "component "Destination - Account" (55)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2012-07-16 11:48:03.65 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-07-16 11:48:03.65 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:47:53 AM Finished: 11:48:03 AM Elapsed: 10.562 seconds. The package execution failed. The step failed.

The first step in the job drops all tables from the DB.

EXEC sp_msforeachtable 'DROP TABLE ?'

The second step executes the package.

I've looked all over online and haven't been able to find a solution. Any help would be appreciated, thanks!

2
ssisbidssql-job
The error message tells us that the dbo.Account table does not exists. Do you actually create it after you DROP it? – Dominic Goulet
You will probably need to modify that to test for the existence of a table. Something like EXEC sp_msforeachtable 'IF EXISTS(SELECT * FROM sys.tables T WHERE T.name = '?') DROP TABLE ?' – billinkc
The Account table exists in the source Access DB. The package creates it. If I execute the package only that is when I get the error not when it attempts to drop the tables. --The flow of the package is basically 1. Create schema for 5 tables at a time 2. Add data for those 5 tables. 3. Create schema for next 5 tables, etc. (This is how the wizard created it). The weird part is that if my package contains only one table (create schema, copy data) it works fine, but with 470 tables is when it encounters a problem. – Goose
@Shannon Do you have these processes running concurrently at any point? Is there a potential for this to ever occcur? It sounds like the step to add dbo.Accounts is executing before the table can be created. Can you provide a screenshot of the flow? Are you using precedence constraints? – dev_etter

2 Answers

0
votes

You have to set delay validation property to true for the second execute SQL task.

0
votes

This took me ages - over a period of months - to nut out.

I was running three SQL tables of data into three Excel sheets in a single workbook. I had all manner of problems with corrupted data - between sheets, even - and OLE errors along with many of the other intermittent errors that people have search for in these threads.

In the end, the issue was that I had the three data flows embedded within a single Data Flow. In hindsight, I guess it was obvious that this would be a problem, but me, being new to SSIS, figured that the processing would be ironed out deeper down. Wrong.

Once I separated the flows into three consecutive Data Flows, everything was fine.

Postscript: After messing about with SSIS over the course of this project I'm convinced that it's probably one of the ropiest pieces of MS product I've come across. Sooooooo much manual configuration - and re-configuration when SSIS suddenly undoes all your tedious work! - changing of data types, connecting this to that. Just horrible. Recommendation: Avoid if possible.