May 26, 2009 at 7:45 am
Hello,
I am trying to make changes to an existing DTS Package. It does something like the following:
'Insert entry if it does not exist in table
mySQLCmdText = "SELECT * FROM
WHERE FILENAME='" & DTSSource("FILENAME") & "'"
'Execute the mySQLCmdText, and put the data into the myRecordset object.
mySourceRecordset.Open mySQLCmdText, mySourceConn, adOpenKeyset
If mySourceRecordset.RecordCount < 1 Then
DTSDestination("SERVER") = "COCONUT"
DTSDestination("MLS") = "M"
DTSDestination("LISTNUM") = DTSSource("LISTNUM")
DTSDestination("FILENAME") = DTSSource("FILENAME")
DTSDestination("SORTORDER") = DTSSource("SORTORDER")
if not isnull(DTSSource("LastModDate")) then DTSDestination("LastModDate") = DateValue(DTSSource("LastModDate"))
Main = DTSTransformStat_OK
Else
Main = DTSTransformStat_SkipRow
End If
[/code]
However I need to add a query that deletes some records before this code block executes:
[code]
'Delete when the file has been replaced by a new file
mySQLCmdText="DELETE FROM [table] WHERE "' AND FILENAME!='" & DTSSource("FILENAME") & "' AND SortOrder='" & DTSSource("SortOrder") & "'"
[/code]
But how do I execute the sql?
I don't think mySourceRecordset.Open mySQLCmdText, mySourceConn, adOpenKeyset
would work in this case. Or would it? Many thanks in advance for your generous help.
May 28, 2009 at 7:19 am
The question is unclear.
It might help if you design the DTS package to execute each block of SQL as a separate step.
You might find it easier to us SSIS, too.
May 29, 2009 at 6:29 am
If I understand this correctly you can use a Task in the DTS package.
In the Package Designer click the drop down menu "Task" then select "Execute SQL Task"
This will give to a window where you can type in any SQL code to execute. Then just link the task into the workflow of your package where ever it needs to be.
Good luck!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply