Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Running DTS packages thru VB 6

Posted on 2005-03-01
Medium Priority
Last Modified: 2008-01-09
I have weekly raw data files that I run VBA macros on to format into .txt to ultimately upload to SQL Database.  I have saved the DTS packages as .vbs files and appended the code to the correspoding formatting Modules in VB 6.  When I execute the code, ultimately the code excutes but the package is not actioned - ie the data is does not upload.

The code is the standard DTS package created with the wizard when saved as a vb script (vbs) simply copied into a VB 6 module.  I have changed the database connection to our production server and I would like to pass in the input file name from another subroutine.

How can I pump data (text files) into a SQL Database using VB/Excel?

Thanks in advance.
Question by:Nicostick

Expert Comment

ID: 13434466
One option is to use a global var in the package and add an activex script to specify the global var as the input for a bulk insert task. In the vb code, you can access and set the value of the global variable.

Author Comment

ID: 13434597
Can you please clairfy - bulk insert task.  If I hard code a file name, the code compiles but the data does not acutally upload to the SQL data base.

Accepted Solution

svid earned 750 total points
ID: 13434704
OK, in your DTS package, you can have a Bulk ?Insert task that specifies the source (a temp file) and a destination table in the required database. Add a global variable to your package (ex: ImportfileName).

Add an ActiveX script with code as follows:

Function Main()
      Dim oPkg, oBulkInsert
      Set oPkg = DTSGlobalVariables.Parent
      Set oBulkInsert = oPkg.Tasks("DTSTask_DTSBulkInsertTask_1").CustomTask

      oBulkInsert.DataFile = DTSGlobalVariables("ImportFileName").Value
      Set oBulkInsert = Nothing
      Set oPkg = Nothing

      Main = DTSTaskExecResult_Success
End Function

In your VB code,

    oPkg.LoadFromSQLServer strServer, strUsername, strPassword, _
        DTSSQLStgFlag_Default, , , , strPackageName
    'Set the Global variables within the package    
    oPkg.GlobalVariables("ImportFileName").Value = strFileName

    For Each oStep In oPkg.Steps
        oStep.ExecuteInMainThread = True

This will set the Global variable to whatever file you specify.

So when the pkg executes, the bulk insert task transfers the data from your file to the specified destination table.
LVL 43

Expert Comment

by:Eugene Z
ID: 13439438
also you can createyour dts package with global variables

and run it with MS DOS sql server utility DTSRUN.EXE from any place VB6,etc.

more: BOL,
LVL 13

Expert Comment

ID: 13477134
Just in case your looking for samples, excellent working examples can be found here:

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question