SQL 2000 Database DTS To Access Questions
-
I have some Questions for those who done solutions with DTS on SQL Server 2000.
I have edited a DTS Package (generated from the Export Data Wizard in Enterprise
Manager) to Produce a Target Access Database containing only records matched to a specific client. However I have not been able to preserve the Primary and Foreign Key relationships between the tables in the Target Access Database.
I realise I am probably going to have to generate these keys using DDL. However I cannot seem to run multiple DDL commands in a single 'Execute Sql task' in the DTS Designer.
1) Is there a way I can run Multiple DDL statements or am I going to have to produce a 'Execute Sql task' per Table or even worse per ALTER statement
2) Also is there a way I can set up dynamic Access connections in the Designer?
(one idea is using DTS Global Variables ??) but I cannot see a way of setting this into the Connection Objects in the Graphical Designer.
Or am I going to have to bite the bullet and drop my packages into Visual Basic 6 and edit them there. I am a little reluctant to do this as
a) the Graphical DTS designer is a little more user friendly to the less code savvy people on my team
b) I want to run the Packages as Jobs on the SQL Server and I cannot see a way of linking a VB Stored DTS Package to Jobs/ Scheduler in SQl server 2000
c) to be honest its been a while since I coded anything in VB6
If anyone can offer some alternatives methods, tools or strategies. I would be grateful to hear from you.
-
" have not been able to preserve the Primary and Foreign Key relationships between the tables in the Target Access Database."
Are you referring to items like auto numbers, etc... This can be done by removing the autonumbers from Accees and exporting the data in order. EG: export parent table then export child table.
"Also is there a way I can set up dynamic Access connections in the Designer?"
Take a look at the Dynamic Properties task. With it you can assign a value to any property that you can edit in Disconnected Edit mode.
"...drop my packages into Visual Basic 6 and edit them there..."
You might need to stoop to writing some VBScript in an ActiveXScript task, but going all the way to VB itself is not really needed.
"...I cannot see a way of linking a VB Stored DTS Package to Jobs/ Scheduler..."
If you write your packages in VB then you would compile an EXE and call that in the scheduled job. Also, providing you haven't got, non-DTS specific VB code, you could also save the package back to the Server, or as a structured storage file. Then you would execute it as normal using DTSRUN.EXE.
--------------------
Colt 45 - the original point and click interface
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply