June 22, 2005 at 10:24 am
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
can you please tell me how to write an active x script for this
June 22, 2005 at 10:52 am
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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply