Post reply

DTS Package

  • June 22, 2005 at 9:20 am

    #61874

    Hi.........

    I need some information.we have a DTS package that is scheduled and it copies data from text file to a table.

    The problem is the fields in the text file may vary.like sometimes it may have less number of fields and sometimes more number of fields.

    now what we are doing is dropping the table and then recreating it manually everytime.is there anyway that it can be automatically done.

    I think this can be done with the help of activex script. Can anyone help me out with this problem.

    Thanks

  • June 22, 2005 at 10:24 am

    #567555

    You can use an Execute SQL Task to drop the table and then use an ActiveX script to create an OpenRowSet query (see BOL for use) that does a SELECT INTO query.  This will require that the text files are comma delimited.

  • June 22, 2005 at 10:33 am

    #567563

    can you please tell me how to write an active x script for this

  • June 22, 2005 at 10:52 am

    #567572

    This code would go into the ActiveX task. You will also need a connection to the database and two Execute SQL Tasks (just put -- in for the query)

    Function Main()

    DIM oPkg

    DIM oTask

    DIM sSQL

     SET oPkg = DTSGlobalVariables.Parent

     SET oTask = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

     sSQL = "DROP TABLE your table name"

     '*** Task to drop table

     oTask.SQLStatement = sSQL

     SET oTask = Nothing

     SET oTask = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask

     sSQL = "SELECT * INTO

     sSQL = sSQL & "FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};" & vbCr

     sSQL = sSQL & "DefaultDir=fully qualified path to file;','select * from file name ')"

     '*** Task to Load table from text file

     oTask.SQLStatement = sSQL

     SET oTask = Nothing

     SET oPkg = Nothing

     Main = DTSTaskExecResult_Success

    End Function

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply