ADODB.Connection Recordset VBScript to VB.Net For SSIS

• Mohan.deval

  Say Hey Kid

  Points: 713

  More actions

  December 2, 2010 at 12:09 pm

  #143747

  Hello

  I have a very very complex ActiveX Script which I Do not want to totally rewrite with ADO.Net or OleDB VB.Net , but use the same skeleton of ADODB Connection & RecordSet Object as is.

  Example VBScript ActiveX Code is:-

  Const ConnectionString = "Provider=SQLOLEDB;Data Source=128.333.45.113;Initial Catalog = MyDatabase;User ID = userId;Password=Pwd;"

  Public Sub Main()

  Set objConn = CreateObject("ADODB.Connection") : objConn.Open ConnectionString

  Dim objSteps : Set objSteps = CreateObject("ADODB.Recordset")

  objSteps.Open" SELECT * FROM tbl_MyTable O " & _

  " INNER JOIN tbl_MyTable_Actions OA ON O.OrderID = OA.OrderID ", objConn

  While Not objSteps.EOF

  ' *** Complex Code Multiple Record Sets, SQL Calls, SP calls, Multiple functions within

  ' **** Like follows...

  colColumns = CreateObject("Scripting.Dictionary")

  Dim objOrder : objOrder = CreateObject("ADODB.Recordset")

  Dim objCust : objCustomer = CreateObject("ADODB.Recordset")

  Dim objServices : objServices = CreateObject("ADODB.Recordset")

  Dim objXXX : objXXX = CreateObject("ADODB.Recordset")

  Dim objDDD : objDDD = CreateObject("ADODB.Recordset")

  Dim objPPP : objPPP = CreateObject("ADODB.Recordset")

  Dim objSub : objSub = CreateObject("ADODB.Recordset")

  Dim objPPCC : objPPCC = CreateObject("ADODB.Recordset")

  Dim objPayment : objPayment = CreateObject("ADODB.Recordset")

  Dim objZ : objZ = CreateObject("ADODB.Recordset")

  ' *** Complex Code Multiple Record Sets, SQL Calls, SP calls, Multiple functions within

  WEND

  Dts.TaskResult = ScriptResults.Success

  End SUB

  Please help....

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  December 5, 2010 at 3:21 pm

  #1258843

  Why not use the standard ActiveX Script Task? You can execute legacy VBScript code like the one you have sent without any changes.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/