MS SQL Server DTS - Dynamically setting datasource for dbf files

Author

Dynamically setting datasource for dbf files

Dave

2005-09-22, 11:24 am

I spent about 4 hours last night trying to solve this problem and
finally found a solution around 1:00AM. I thought I would share it
with you guys so the next guy won=E2=80=99t spend so much time looking =EF=
=81=8A

I was trying to loop though a bunch of folders (one folder per store)
and set the DataSource property of one of the package=E2=80=99s connections.
oPkg.Connections("DBF").DataSource =3D =E2=80=9Cc:\data\sto
re1=E2=80=9D

The code updated the datasource property, but it kept throwing errors
each time it tired to transform some data. The first pass always
executed correctly.

I was getting the following error
Microsoft Jet Database Engine Could not find the object=E2=80=A6.

Anyway, I found the solution posted here.
http://support.jodohost.com/showthread.php?t=3D1192

The trick is to create an empty SQL task as the step after you change
the datasource and tick close connection on completion.
Workflow-->workflow properties-->Option

I hope this post saves someone the headache I had.

Sponsored Links