DTS Package

Question

DTS Package

pinky

SSCommitted

Points: 1997
More actions
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

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

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

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 1

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.

pinky SSCommitted Points: 1997 More actions · Answer 2

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

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 3

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