DTS execution via MS Access

[joe789]

Hi Folks,

I am wondering if it is possible to execute a DTS package via Microsoft Access. I have done a bit of research and cannot seem to really find anything that claims this is possible or how to go about to get it to work. I am running SQL Server 2000 which has quite a few DTS packages ... it would be really neat if I could just create a small interface in Access whereby a form has a few buttons on it that can be clicked and the DTS packages can be executed on the server, this way folks won't need to log on to the server and run the DTS packages via SQL Server Enterprise Manager. What would REALLY be awesome would be if values can be passed between SQL and Access but I think if that is possible it would be quite an animal to achieve (I know it is possible via Visual Basic programming but not really worth all the work to create an independent program just to run a DTS package or pass a value). If anyone can provide me with any information or where to look I would appreciate it.

Thanks,

Joe

 

[boblarson]

Yes, you can and I do.

Here is a sample database

This sample has two ways of doing it.
1. by referring to the DTS package in SQL
2. by saving the DTS package as a script and incorporating it.

I created this for testing to see what I could do for a project we had here.

 

[pbaldy]

Interesting Bob, as I've had to create an SP that started the package. What is the reference you have to DTS, which I don't seem to have available? Neat trick, in any case.

 

[boblarson]

If you have the SQL Server Tools installed it works fine, but if you don't there are a couple of dll's you gotta have. My coworker came up with which dll's but I can't remember at the moment.

The reference is:

Microsoft DTSPackage Object Library