SQL Server Performance Forum – Threads Archive
DTS GlobalVariable Question
Hi folks, <br /><br />I’m having a little conceptual trouble with a DTS package: <br /><br />The goal is to update a table in sql server with only the latest rows in a table in a sybase db, based on an identity row. <br />So, I can obviously set up the connections, and I’ve been able to get the max identity row from the Sql server (ReceiptID) into a global variable (MaxReceiptID, not surprisingly). <br /><br />From there, what’s the most efficient method of selecting (from the Sybase db) only the rows w/ ReceiptID > MaxReceiptID and importing just these into the sql server? <br /><br />I want to try something like: <br />SELECT myfield1, myfield2, ReceiptID <br />FROM mySybaseTable <br />WHERE ReceiptID > @MaxReceiptID <br /><br />but I can only find examples of referencing the global variable if I’m using an activeX script. (DTSGlobalVariables("MaxReceiptID").value – from<a target="_blank" href=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_misc_68tv.asp>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_misc_68tv.asp</a>) That method gets me all sorts of ‘must declare the variable’ errors and/or syntax errors if I try declaring & setting it. <br /><br />If I create and call an activeX script (see *) for the transformation, then for some reason, the if-then statement is ignored (or maybe the whole activeX script), and all of the rows are copied into sql server every time the package is run. I’ve not used activeX scripts in DTS packages much, so I could be completely missing something there. I’ve also not used global variables in DTS packages, so it could be something there. <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> <br /><br />Thanks! <br />-Rachel <br /><br />* One similar to: <br />Function Main() <br /> if DTSSource("receipt_id") > DTSGlobalVariables("MaxReceiptID").value then <br />DTSDestination("field1") = DTSSource("field1") <br />DTSDestination("field2") = DTSSource("field2") <br />… <br />Main = DTSTransformStat_OK <br /> end if <br />End Function <br /><br /><br />
You should be able to something like this in an Execute SQL Task SELECT myfield1, myfield2, ReceiptID
FROM mySybaseTable
WHERE ReceiptID > ? and then click on the parameters button and map the GV to the parameter in the query.Not entirely sure this will work with a non SQL Server connection but don’t see why not. HTH Jasper Smith
Thanks Jasper. It actually doesn’t seem to work with a non-sql server connection. I created the execute sql task, stuck in the sql I wanted, and clicked on the parameters button, but there’s no drop down under the input parameters mapping box to choose a GV. If I go to ‘create global variables’, MaxReceiptID is listed, I just can’t choose it. If I switch my connection to the Sql Server, I get the drop down and can execute the sql without a problem. Any other ideas?
]]>