SSIS Table Created in Control Flow but causing errors in Data Flow

Question

I am new to SSIS, and I cannot quite get this to work. I have two tasks in my Control Flow. The first is an Execute SQL Task that does 3 things. 1. Checks to see if a database exists. 2. Checks to see if a table exists in that database. 3. If the table does not exist, it creates the table.

Then I have a Data Flow task that loads data from an excel spreadsheet into the table created from the Execute SQL Task.

If I execute each task separately, everything works fine. The table is created if necessary, and the data is loaded.

If I execute the package as a whole, I get an error, because the table doesn't exist. How do I get the Execute SQL Task to create the table THEN perform the Data Flow task to load the data from the excel spreadsheet?

I've included a link to my control flow, if that helps. https://i.stack.imgur.com/Z7dlX.png

Here is the SQL that is executed.

use master
go
[enter image description here][1]if exists (select 1 from sys.sysdatabases where name = '001101_sisdb')

use [001101_sisdb]
go
if not exists (select 1 from sys.tables where name = 'productivity_core_expense_ratio')

    create table productivity_core_expense_ratio (
    fiscal_year int
    ,ipeds_code nvarchar(6)
    ,school_name nvarchar(150)
    ,instruction float
    ,academic_support float
    ,student_services float
    ,public_service float
    ,research float
    ,institutional_support float
    ,num_core_expense float
    ,den_core_expense float
    ,core_expense_ratio float
    )

Kobi Kobi · Accepted Answer · 2018-03-13T16:46:48

You can set the package property Delay Validation to True in order to tell SSIS not to check if all objects ( dataflow etc..) are valid before execution time.

SSIS Table Created in Control Flow but causing errors in Data Flow

1 Answers