CODEWITHSUNDEEP
ssis
cryocaustik
cryocaustik

Reputation: 479

SSIS - Use SSIS to Create SQL Table, Load Data, Run Code and Drop Table

I am kinda new to SSIS and apologize in advance if this is a repeat post, or simply a dumb question.

I am trying to create the following process in SSIS:

1- [SQL Execute Task] Create Table in SQL DB 
2- [Data Flow Task] Load Data from a source file (.xls) into the Created SQL table 
3- [SQL Execute Task] Run Code on Created SQL table 
4- [SQL Execute Task] Drop the SQL table that was created

The problem I am running into is when I set my OLE DB Destination it wants a table that is already created. I tried to create the table and then run the process, it works the first time, but errors the 2nd time saying the table doesnt exist, even though it is skipping step 1 of creating the table.

Any ideas on a work around, or am I missing something very obvious here?

Thanks in advance!

Upvotes: 1

Views: 1558

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

So first, why drop the table every time? Your package is going to require consistent metadata for the table, so why not just truncate it and save it for the next load? This is a really kind of terrible approach to SSIS packages.

The reason it's failing is because SSIS does both design-time and runtime validation of all your components, so all it sees is the table's not there that it expects to be there.

But if your heart's set on this approach, you need to set the ValidateExternalMetadata property of your destination component to false. As long as the External Columns on the component match the actual columns being generated by your CREATE TABLE statement, you'll be good to go.

Upvotes: 3

Related Questions