SSIS Runs Okay Individual Tasks, Not Together

Question

I have a simple SSIS Project. In the control flow I have three steps:

Step 1: Select Data from Db1.Table1

Step 2: Create Table2 in Db2

Step 3: Copy Data in Db1.Table1 to Db2.Table2

If I "Execute Task" one by one in order, it executes fine...but if I try running the entire project I receive the following error:

Error at Copy Data from Table1 to DB2 dbo Table2 Task [OLE DB Destination[40]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E37 Description: "Invalid object name 'DB2.dbo.Table2".".

Answer 1

Your control flow makes no sense to me. There is no reason at all to havea step that only selects records. When this runs as a job who would see it and if you aren;t doing anything with them then why select.

If you are doing no cleanup or transformations why are you even using SSIS? Wouldn;t a t-sql stament of SElect into table2 from table1 be sufficient? Of course this is problem if running more than once, so I would probably write a script to create the table if is doesn't exist, truncate it is does (I'm assuming you are replacing the data here) and then insert into it.

Answer 2

The issue was with the validation. On child tasks by default DelayValidation is disabled. Since the second step creates a table which is in the third step populated with the data pulled in the first step, there is a validation error. SSIS looks for the table and it doesn't exist. By enabling DelayValidation it operates fine - since it doesn't force the tables to be in existence before executing.