How to retrieve Global Variables in ActiveX VB Script in SSIS

• Michael Ryan

  SSC Rookie

  Points: 35

  More actions

  October 27, 2006 at 6:29 am

  #117343

  I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package.  In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:

  ==========================================

  Function Main()

  Response.Expires=-1

  set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

  objBL.ConnectionString =

                 "provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"

  objBL.KeepIdentity = False

  objBL.CheckConstraints = False

   objBL.Execute  DTSGlobalVariables("gv_XSDSchemaFile").Value,  DTSGlobalVariables("gv_XMLFullPath").Value

   

   Main = DTSTaskExecResult_Success

   set objBL=Nothing

  End Function

  =========================================

  I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer.  I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination.  This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used.  (They do provide this functionality for the XML file source though). 

  My requirement is to allow for the Global Variable to be passed for the Schema file at runtime.  The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.

  Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...

  Michael