Importing foreign tables where format has changed

[schusser]

Hello all,
I have a DTS package that imports a series of tables (from a third party application) into SQL Server. A few months ago, the format of some of the input tables was changed as a result of an application upgrade. Fields added, fields deleted, etc. My package continued to work, but I was not receiving the new fields, and any fields that had been removed were now empty in SQL Server. There was no 'heads up' from the vendor indicating these changes.

I use drop, create, then connect to import, for each table, but the create continues to create the old table format, and ignores new fields.

Is there a way to receive notification from the DTS package when a change is made to the incoming table format? Or, is there a way to identify in my own applications, reports, stored procedures, etc., where these tables are used. I'm thinking there might be some utility that I haven't found yet.

Thanks in advance.

 

[gradley]

First to address the issue of the old format still being used:

I assume you are using the transform data task to import the files? If so, you want to go into the task by double-clicking the transform portion between the 2 connections. Then if you select the "transformation" tab, you can select "Delete ALL" to remove all existing transformations. Then you can recreate the transformations based on the new format.

As for DTS notifying you of a change, I think the only way that will happen is if a field data type has changed (number to string) or if you are now receiving less fields than before. The package will then fail -- of course that's not what you want to happen.

The best advice I can give you, is to somehow check the format ahead of time (if the vendor is not notifying of such changes). Depending how the file is delimited, you could initially bring the file in as a flat file and then check the number of columns (ex: Pipe delimited, check number of pipes in the first row). Just a suggestion.

 

[Catadmin]

Your problem sounds like the DTS hasn't been told that there are new columns to transform. Even if you drop and recreate the table, until you edit the package (or create a new one) to include those new columns and exclude the deleted ones, it's going to keep trying to do the same old thing over and over again.

As far as which is easier, editing or creating a new DTS package, it depends on how complicated a package you had set up.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???