Updating DTS to point to a new DataSource

Question

Updating DTS to point to a new DataSource

LadyReader

Ten Centuries

Points: 1192
More actions
July 25, 2006 at 1:16 pm

#68333

I have a DTS that uses an ActiveX script to copy some files from a dated folder to a non-dated folder each day, and then, if that step succeeds, the DTS continues with transformations which load each file into its own SQL table.
I recently had to switch the source of the data and I went through every step, changing the folderpath as needed. I tested the DTS and I believe it is working correctly. However, today when I saved the DTS as VB, I noticed that the Data Sources in the .bas module still refer to the old folderpath.
When I look at the copy step and the transformation step all references I can see point to the new folderpath. To be sure, I renamed one of the files in the old folderpath so that that filename referenced by the DTS did not exist in the old folder and I raran the DTS. It ran without any errors.
So, can anyone tell me where the old folderpath reference is hiding? Can anyone explain what's going on? I am mostly self-taught so please be precise.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Don Staten SSC Eights! Points: 957 More actions · Answer 1

There are a couple of things that I can think of that may be causing this to happen:

(Most unlikely) - Too many versions of the DTS package. I normally keep only a few versions of the DTS package around in case recent changes do not perform as expected. To check versions of the package, right-click the package in EM and select "Versions...". Clear out as many version as you wish. Keep in mind that once they are deleted, they are gone for good.

(Most likely) - You may have to perform a Disconnected Edit within the DTS package to clean up the data sources to the connections. To do this, open the package. Then, without selecting any objects, select from the menu -"Package" -> "Disconnected Edit...". Expand the Connections node and then check each connection. Just click a connection, then look at the "DataSource" variable on the right. Make any appropriate changes, save the package, then reopen it.

LadyReader Ten Centuries Points: 1192 More actions · Answer 2

Hi Don - re: Disconnected Edit - that's it! Thank you so much. I never noticed that option before.

Thank you again for your help and for taking the time to reply!

Lady Reader

LadyReader Ten Centuries Points: 1192 More actions · Answer 3

Don - one more thing: now that I know where to look, can you tell me why these references are wrong when I have changed them in the body of the DTS? And, if the DataSource is wrong as I saw in the VB code and in the Disconnected Edit area, can the DTS still be working correctly?

Don Staten SSC Eights! Points: 957 More actions · Answer 4

In my experience, I've found that the "synch" issue normally occurs if there are two or more instances of the same connection (like referencing the same Excel file for two different transforms). As to the technical reason as to why it happens, I'm not sure. I chocked it up as one of "those" problems within SQL 2000 and I just know I need to perform the disconnected edit to resolve it.

It is entirely possible that the DTS package will not work correctly with the data sources out-of-synch. But then again... maybe it will work

Perhaps someone else could shed more light on the behind-the-scenes action here?

LadyReader Ten Centuries Points: 1192 More actions · Answer 5

Ahh, another one of "those"!

I am assuming you meant to say "abnormally". It sure looks like the DTS is connecting to the right folder.... but I am editing via Disconnected Edit just to be sure.

Thanks again for your insights.