SSIS package will succeed with one table, but not all?
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  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  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!
You have to set delay validation property to true for the second execute SQL task.
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.