connection nonexistent at runtime

The problem:

I have a package that does this

1. Deletes a SQL Server database.

2. Recreates it with the tables ready to take data.

3. Executes a Flow Task that puts the data into the tables.

So far so good. The problem I have is that everything

is loaded correctly as long as the tables are in place

before I try to execute the package. When the first

step deletes the database I get an error because

the connection manager cannot find the tables that

are defined in the database that has been removed

and the package seems to disregard the fact that before

the data gets loaded the whole structure will already

be in place. What should I do to convince the package

that the connection manager will have the target

database set up when the data needs loading?

Thanx for any ideas.

Why are you deleting the database? Why not truncate the tables?

One thing you can do is to set "DelayValidation" to true on your connection manager objects. It might also help to set "ValidateExternalMetadata" to false on the OLE DB source/destination objects.|||

Well, still does not work. For some reason the managers want to

ensure that the connection is available before they actually need it.

I can't truncate the tables because I have been told not to. I have to

get rid of the database altogether.

|||

Darlove wrote:

Well, still does not work. For some reason the managers want to

ensure that the connection is available before they actually need it.

I can't truncate the tables because I have been told not to. I have to

get rid of the database altogether.

That's the silliest thing I've heard in a while... Recreating a database is a better thing to do? Since when? But anyway...

The connection managers do validate, but that's the point of setting DelayValidation = True.

You might want to create to packages and then call them in a master package by using Execute Package tasks. The first "child" package to delete and recreate the database, and the second to populate the tables.

It's still 100% better idea to use an Execute SQL Task to truncate the tables before moving into the data flow. Truncate is a non-logged operation, so I'm curious why it's use should be avoided. "Delete from table" is a logged operation and I can see why that would be avoided, but not the truncates.|||

Well, I am not the one to blame for the silliness.

I just do what I am told to. Anyway, I will try your

'master-child package' approach and let know about

the results. Thanx.

|||

Darlove wrote:

Well, I am not the one to blame for the silliness.

I just do what I am told to.

Yep, I know. But you might want to inquire as to "why" you shouldn't truncate tables and "why" dropping and recreating a database everytime is a good idea.