SSIS package - create, use then drop table

[mossbs]

Hi guys,

Have built this SSIS package that imports data from a csv into the database and then runs a load of sql commands based on the data in there to insert new users, roles etc etc into the relevant tables in the database.

however i wanted to do this without having to create a table in the database that would stay there merely for this import routine.

So have added SQL task the data flow to create a table, then after the last SQL task to drop the table.

However now on running it, it throws the inevitable error message that the destination table in my data flow does not exist.

Basically i want to create the table, se it for the data import, then after all is done, drop the table to kind of tidy up after myself.

Am i missing something here? Or is there a way to do this that I am not seeing?


Cheers guys

 

[SQLBill]

Why do you want to keep recreating the table? Is it really necessary?

Since you keep reusing the table, create it once then have your SSIS package Truncate the table, then repoplulate it.

As for the failure you are seeing...are you creating a temporary table (#tablename)? If so, you may be going 'out of scope' and the table may not exist any longer.

How was the table created? Did you create it using dbo.<tablename> and are you inputing to it using dbo.<tablename>? If not, maybe the owner name is different between the two steps.

You could try opening the package and executing each step separately and then using SSMS to double-check the results.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875