Home > SSIS Best Practices > Check if a table exists

Check if a table exists

Some developers like to drop and recreate physical staging tables on the fly during ETL process, instead of just truncating the table. The advantage is that it will have no data type mismatch. The disadvantage is when the process failed, and the table recreation failed, any processes that depend on the existence of the staging table will fail too.

I recently ran into this problem when a co-worker’s ETL failed, and the staging table my process depends on was dropped and not recreated.

To avoid such problem in the future, I modified my script to check if the staging table exists before using it:

image

If I don’t want to use T-SQL, I can do it in SSIS package with a user variable:

1) Create a package level variable User::varTableCnt

2) In a SQL task, write a SQL script like this, and set Result Set to ‘Single row’.

       select    count(*)  as TableCnt
        from    EAM_ETL.information_schema.tables
        where    table_catalog = ‘EAM_ETL’
        and        table_type = ‘BASE TABLE’
        and        table_schema = ‘theSchema’
        and        table_name = ‘theTable’

EAM_ETL is the database where the staging table resides.

2) In the same SQL task, on the Result Set tab,

  • Result Name = TableCnt
  • Variable Name = User::varTableCnt

3) Then, in the SQL task’s Precedence Constraint Editor,

  • Evaluation operation = Expression
  • Expression = @varTableCnt == 1

4) Last create another SQL task to do what I need to do when the staging table exists.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: