Calling saved DTS package from VBA

[TomCarnahan]

I am new at working with SQL Server DTS and executing it from VBA. I am having difficulty getting a DTS package (that works perfectly from Enterprise Manager - DTS Designer) to complete its process when I call it from VBA.

My saved DTS package in SQL Server DTS Designer performs the following steps.

1) Drop the table
2) Create the table with Prime Key
3) Create a UNIQUE NONCLUSTERED index on Field_UNIQ>
4) Imports data from a .CSV flat file
5) Updates several other fields



The package works great from Enterprise Manager. The only thing that doesn't completely execute is Step 4 (above) because the data I am importing has duplicates in my field <Field_UNIQ>. However, looking at the result set, it appears that it did what I wanted because it imports the non-duplicate records.

Note: I don't want to import records that have non-unique values in <Field_UNIQ>. While it would be nice to get some feedback as to how many records were skipped, it is not critical at this point.

QUESTIONS:

1) Is another there way to call the saved "server"
DTS packages from VB? (I have not been able
to execute 'dtsrun' from a stored proc; see below)?

2) Is there a way to re-work the import step
in DTS Designer so that the process won't
abort the entire process when it finds its first duplicate value in <Field_UNIQ> (see below)?

(It doesn't do this from DTS Designer;
only when I call it from my "dtsExecutePackage"
script on the client. Note: I am trying to
keep the client thin)

3) Is there another way to do this and keep my client thin?



--------------------------------------
HERE IS WHAT I TRIED:

I tried using "master xp_cmdshell 'dtsrun parameters>'" from a Stored Procedure. (This is not the exact syntax and unfortunately I don't have the code here) The error message kept saying that I didn't have permissions. I tried calling it with the option that says to use Windows permissions / authentication. I tried then to call it with user = "sa". I kept getting the same error.

I had my SQL Server "guru" buddy (who has since left the company) work with me on getting "dtsrun" to work, but neither of us could do it. We suspect that the non-resident DBA has security set so that we can't use it. Because I am a sub-contractor onsite with the customer, getting them to change security settings won't work.

My next observation was that I could save my saved DTS Designer package as a .sql script file, however, I could never find a way to launch a .sql script from inside SQL Server or the client. I suspect that .sql files are only for transporting or archiving server objects, or for saving queries from Query Analyzer.

Next, I saved the DTS package to a file as a VB Script (.bas file) and incorporated the code into my project. I linked my VBA project into the .DLL that supports this. The process works fine, but I was surprised at the amount of code require on the client. This runs counter to my keeping the client thin, so I kept looking.

The next thing I tried was saving the DTS package as a .dts file on the server and using a "dtsExecutePackage" example that I found in the SQL Online Books. I got it to run, but it would not import any records. The feedback I got from the example's error handler was cryptic, but indicated that it aborted in mid-task. I finally figured out that the process is stopping on Step 4 (Import data). It is getting to record 2 or 3, finding a duplicate <Field_UNIQ> value, and aborting the entire routine without committing any records. This doesn't happen when I execute my saved package from DTS Designer.

In Step 3 (Create a UNIQUE NONCLUSTERED index on <Field_UNIQ> ), I used the option "IGNORE_DUPES" (I think that is the way it is spelled ... I copied and pasted it from Help) which the help file says will allow INSERTS on ALL records that do not have duplicate values in that field. It appears to work fine from DTS Designer, but the .dts file I saved, when called from VBA still aborted the entire import process.

The End
------------------

Thank you ahead of time for any suggestions or examples you can provide!







--- Tom

 

[TomCarnahan]

In the process of trying to eliminate the volumes of code that saving my DTS package to VB creates, I happened upon a cool way to execute a DTS package that doesn't require much code.

By using the "MS DTS Package Object Library" reference, I was able to create a "TS_Package2" object. One of its methods is "LoadFromSQLServer". This command allows you to reference a DTS package that has been saved on SQL Server and execute it. The code is not very long and it is fast.

This process is more functional that just calling stored procedures because you can string multiple tasks together and cause them to execute sequentially, plus easily provide for the situation where the task does not execute successfully. Check it out!

--- Tom