January 12, 2004 at 10:53 am
I can't quite wrap my brain around this one, displaying my obvious newbie-ness.
I have a DTS package where I want to execute certain steps only if there are rows in a view ("SelDupDels"). I thought I could access the view through an ActiveX task, and either Succeed or Fail the task depending on the row count in the view.
However, I can't seem to figure out how to get access to the data. I copied similar VBScript from an ASP page (told you I was a newbie) but it fails on the "Set conn = Server.CreateObject('ADODB.Connection')" statement.
Are ActiveX DTS Tasks capable of accessing data? How do you specify the connection? I thought maybe using a Connection Object, but couldn't find anything on that in BOL (did I miss it?).
Any pointers would be greatly appreciated.
ps.
I also tried used a multi-phase datapump approach, specifying an Insert Failure script:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
Function InsertFailureMain()
InsertFailureMain = DTSTransformStat_SkipInsert
End Function
Based on the documentation this should have allowed the table to load, just skipping duplicate primary keys. Instead the load aborted with a primary key constraint error....
January 12, 2004 at 1:37 pm
You can use a ExecuteSQL Task and assign the rowcount to a global variable in the paramters mapping/output parameter tab. Then evaluate the new global variable in a workflow ActiveX script for each conditional step.
Example of workflow script:
IF DTSGlobalVariables("gvMyNewVariable").Value > 0 THEN
Main = DTSStepScriptResult_ExecuteTask
ELSE
Main = DTSStepScriptResult_DontExecuteTask
END IF
January 12, 2004 at 4:01 pm
> but it fails on the "Set conn = Server.CreateObject('ADODB.Connection')" statement.
Remove the "Server." and use double quotes instead of single quotes. Both of these items will give you an error. The "Server." will give you an "object required" error and the single quotes will give you a compilation error.
Set conn = CreateObject("ADODB.Connection")
January 12, 2004 at 11:05 pm
Thanks both. I'll have to look into assigning the output of a query to a global variable. I know the theory, but never did it. The second answer leads me directly to a solution I understand.
Nobody's ever used a multi-phase data pump task like I noted?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply