CODEWITHSUNDEEP
sql-serverssisoledb
Maryna
Maryna

Reputation: 231

SSIS returns an incorrect error

I created SSIS package, which created table MyTable in SQL Server with a column BaseVariantVersionID. Program first inserts data into this table.

At the end of the package, I should drop column BaseVariantVersionID from the table.

First debug is OK. But on second attempt, SSIS returns a validation error. It doesn't allow to recreate the table with BaseVariantVersionID, because on next step package cannot made insert in column, which now is not presented.

Maybe you know some property to disable current db checking?

Update

I drop column after all steps. And on first step I recreated DB with column. But system returns error - looks like it use existing table for validation.

enter image description here

Upvotes: 5

Views: 212

Answers (3)

Zorkolot
Zorkolot

Reputation: 2027

This could be several issues I can think of.

You must absolutely delete the table if it already exists prior to creating it at the beginning of the package.

Example Execute SQL Task:

IF OBJECT_ID('MyTable', 'U') IS NOT NULL
    DROP TABLE MyTable
GO

CREATE TABLE MyTable (etc...) GO
ALTER TABLE MyTable ADD COLUMN (etc...) GO

Second, you can set DelayValidation = True in the Data Flow Task Properties Window (this is on the bottom right usually after clicking a Dataflow Task in the design area). That delays validation until run time.

If at the moment, you have a missing field error, you can add the column manually in SQL Server Management Studio, then double-click the task with the error and any missing field error should disappear (now that the column exists). After that you can save the package and exit.

Upvotes: 3

criticalfix
criticalfix

Reputation: 2870

For an Execute SQL Task, you can set BypassPrepare to True. Sometimes that will allow you to design and build a package which doesn't validate at design time, but will validate okay at run time.

But I have to question the need to create columns and tables at run time. Are you sure you need to do this? It is a more typical use case to have SSIS move data around in existing table structures, not create them at run time.

Upvotes: 2

DaveE
DaveE

Reputation: 3647

If I read your description correctly, you're dropping the column on the first pass, then getting an error trying to recreate the table on the second pass? You will get an error on run #2 if you try to create a table that already exists, even if it doesn't have "SomeColumn" in it.

You'll need to drop the table if you're going to want to recreate it or change your code to add the column back if the table exists.

Upvotes: 0

Related Questions