DTSGlobalVariables.Parent to SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTSGlobalVariables.Parent to SSIS

Author

Topic

abhijaiswal.84
Starting Member

29 Posts

Posted - 2012-05-01 : 05:07:36

Hi,

I am converting a DTS package to ssis and getting some error saying "DTSGlobalVariables.Parent" is not accessible to ssis, my code below is in VB Script. Please help me on this front.

' 243 (Excel Named Range)
Option Explicit

Function Main()

Dim sActualLocationOfData
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim oPkg
Dim oConn

' Location, which sheet and in which cells is our Data.
' This will produce a string like Sheet1!R14C8:R11C43

sActualLocationOfData = "=Sheet" & CStr(DTSGlobalVariables("SheetNumber").Value) & _
"!" & DTSGlobalVariables("DataLocation").Value

' Create and set up the Excel File to Import
Set Excel_Application = CreateObject("Excel.Application")

' Open Excel Workbook
Set Excel_WorkBook = Excel_Application.Workbooks.Open(DTSGlobalVariables("FileLocation").Value)

' Get the Worksheet
Set Excel_WorkSheet = Excel_WorkBook.Worksheets(Cint(DTSGlobalVariables("SheetNumber").Value))

' Tell Excel where to get the data and add a named range to the workbook.
' The DTS pump is expecting a source table of "ImportTable" so we name our range that.
Excel_WorkBook.Names.Add "ImportTable", sActualLocationOfData

' Save the changes back to the workbook. If you fail to do this then you will get
' a message box asking you if you want to make changes to the Excel spreadsheet
Excel_WorkBook.Save

' Clean Up Excel Objects
Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing

' Now set the Excel Filename on the Connection
Set oPkg = DTSGlobalVariables.Parent
Set oConn = oPkg.Connections("Excel File")

oConn.DataSource = DTSGlobalVariables("FileLocation").Value

' Clean Up DTS objects
Set oConn = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

Thanks in advance.

Rgds,
Abhi

Abhishek

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-01 : 07:26:19

Please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=174159

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources