SSIS returns an incorrect error

Question

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.

Answer 1

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.