?
Solved

Running DTS packages thru VB 6

Posted on 2005-03-01
6
Medium Priority
?
446 Views
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.
0
Comment
Question by:Nicostick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 5

Expert Comment

by:svid
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.
0
 

Author Comment

by:Nicostick
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.
0
 
LVL 5

Accepted Solution

by:
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
    Next
    oPkg.Execute

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.
0
 
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,
http://www.databasejournal.com/features/mssql/article.php/3086891
0
 
LVL 13

Expert Comment

by:TommyTupa
ID: 13477134
Just in case your looking for samples, excellent working examples can be found here:
http://www.sqldts.com/default.aspx?208
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

765 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