Solved

VB6 DTS code from SQL/Office 2000 -- Upgrade to Sql 2005/Excel 2007

Posted on 2008-06-09
3
657 Views
Last Modified: 2013-12-25
Hi -

I have an old DTS package that was code generated by sql 2000.  We've upgrade to 2005 which was not an issue but now we'd like to upgrade to office 2007 as well.  The DTS package pushes excel data into sql server but it no longer functions with office 2007.  We could upgrade and save in the old .xls format but I'd like to move over to the xlsx or xlsm format.  Does anyone have suggestions on how/the best way to do this.  An ex of an old dts package is attached

Private Sub Import_Pricing()

        Set goPackage = goPackageOld
 

        goPackage.Name = "Gopher2"

        goPackage.description = "DTS package description"

        goPackage.WriteCompletionStatusToNTEventLog = False

        goPackage.FailOnError = False

        goPackage.PackagePriorityClass = 2

        goPackage.MaxConcurrentSteps = 4

        goPackage.LineageOptions = 0

        goPackage.UseTransaction = True

        goPackage.TransactionIsolationLevel = 4096

        goPackage.AutoCommitTransaction = True

        goPackage.RepositoryMetadataOptions = 0

        goPackage.UseOLEDBServiceComponents = True

        'goPackage.LogToSQLServer = False

        'goPackage.LogServerFlags = 0

        'goPackage.FailPackageOnLogFailure = False

        'goPackage.ExplicitGlobalVariables = False

        'goPackage.PackageType = 0

        
 

Dim oConnProperty As DTS.OleDBProperty
 

'---------------------------------------------------------------------------

' create package connection information

'---------------------------------------------------------------------------
 

Dim oConnection As DTS.Connection
 

'------------- a new connection defined below.

'For security purposes, the password is never scripted
 

Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
 

        oConnection.ConnectionProperties("Data Source") = "C:\Myfile.xls"

        oConnection.ConnectionProperties("Extended Properties") = "Excel 8.0;HDR=YES;"

        

        oConnection.Name = "Connection 1"

        oConnection.id = 1

        oConnection.Reusable = True

        oConnection.ConnectImmediate = False

        oConnection.datasource = "C:\Myfile.xls"

        oConnection.ConnectionTimeout = 60

        oConnection.UseTrustedConnection = False

        oConnection.UseDSL = False

        

        'If you have a password for this connection, please uncomment and add your password below.

        'oConnection.Password = "<put the password here>"
 

goPackage.Connections.Add oConnection

Set oConnection = Nothing
 

'------------- a new connection defined below.

'For security purposes, the password is never scripted
 

Set oConnection = goPackage.Connections.New("SQLOLEDB")
 

        oConnection.ConnectionProperties("Persist Security Info") = True

        oConnection.ConnectionProperties("User ID") = "myuser  "

        oConnection.ConnectionProperties("Initial Catalog") = "mydb"

        oConnection.ConnectionProperties("Data Source") = "myserver"

        oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"

        

        

        oConnection.Name = "Connection 2"

        oConnection.id = 2

        oConnection.Reusable = True

        oConnection.ConnectImmediate = False

        oConnection.datasource = "myserver"

        oConnection.ConnectionTimeout = 60

        oConnection.Catalog = "mydb"

        oConnection.UseTrustedConnection = True

        oConnection.UseDSL = False

        

        'If you have a password for this connection, please uncomment and add your password below.

        oConnection.Password = "mypassword
 

goPackage.Connections.Add oConnection

Set oConnection = Nothing
 

'---------------------------------------------------------------------------

' create package steps information

'---------------------------------------------------------------------------
 

Dim oStep As DTS.Step

Dim oPrecConstraint As DTS.PrecedenceConstraint
 

'------------- a new step defined below
 

Set oStep = goPackage.Steps.New
 

        oStep.Name = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Step"

        oStep.description = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Step"

        oStep.ExecutionStatus = 1

        oStep.TaskName = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task"

        oStep.CommitSuccess = False

        oStep.RollbackFailure = False

        oStep.ScriptLanguage = "VBScript"

        oStep.AddGlobalVariables = True

        oStep.RelativePriority = 3

        oStep.CloseConnection = False

        oStep.ExecuteInMainThread = True

        oStep.IsPackageDSORowset = False

        oStep.JoinTransactionIfPresent = False

        oStep.DisableStep = False

       

        

goPackage.Steps.Add oStep

Set oStep = Nothing
 

'---------------------------------------------------------------------------

' create package tasks information

'---------------------------------------------------------------------------
 

'------------- call Task_Sub1 for task Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task (Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task)

Call Task_Sub1(goPackage)
 

'---------------------------------------------------------------------------

' Save or execute package

'---------------------------------------------------------------------------
 

'goPackage.SaveToSQLServer "(local)", "sa", "mypassword"

goPackage.Execute

goPackage.UnInitialize

'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line

Set goPackage = Nothing
 

Set goPackageOld = Nothing
 

End Sub
 
 

'------------- define Task_Sub1 for task Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task (Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task)

Public Sub Task_Sub1(ByVal goPackage As Object)
 

Dim oTask As DTS.Task

Dim oLookup As DTS.Lookup
 

Dim oCustomTask1 As DTS.DataPumpTask

Set oTask = goPackage.Tasks.New("DTSDataPumpTask")

Set oCustomTask1 = oTask.CustomTask
 

        oCustomTask1.Name = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task"

        oCustomTask1.description = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task"

        oCustomTask1.SourceConnectionID = 1

        oCustomTask1.SourceSQLStatement = "select `Ticker/Cusip`,`Ticker`,`Px_Mtd_Close`,`Unit_value`,`Date` from `Sheet1$`"

        oCustomTask1.DestinationConnectionID = 2

        oCustomTask1.DestinationObjectName = "[Mydb].[dbo].[Daily_Security_Pricing]"

        oCustomTask1.ProgressRowCount = 1000

        oCustomTask1.MaximumErrorCount = 0

        oCustomTask1.FetchBufferSize = 1

        oCustomTask1.UseFastLoad = True

        oCustomTask1.InsertCommitSize = 0

        oCustomTask1.ExceptionFileColumnDelimiter = "|"

        oCustomTask1.ExceptionFileRowDelimiter = vbCrLf

        oCustomTask1.AllowIdentityInserts = False

        oCustomTask1.FirstRow = 0

        oCustomTask1.LastRow = 0

        oCustomTask1.FastLoadOptions = 2

 

        

Call oCustomTask1_Trans_Sub1(oCustomTask1)

                

                

goPackage.Tasks.Add oTask

Set oCustomTask1 = Nothing

Set oTask = Nothing
 

End Sub
 

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
 

        Dim oTransformation As DTS.Transformation

        Dim oTransProps As DTS.Properties

        Dim oColumn As DTS.Column

        Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")

                oTransformation.Name = "DirectCopyXform"

                oTransformation.TransformFlags = 63

                oTransformation.ForceSourceBlobsBuffered = 0

                oTransformation.ForceBlobsInMemory = False

                oTransformation.InMemoryBlobSize = 1048576

          

                

                Set oColumn = oTransformation.SourceColumns.New("Date", 5)

                        oColumn.Name = "Date"

                        oColumn.Ordinal = 5

                        oColumn.Flags = 118

                        oColumn.Size = 0

                        oColumn.datatype = 7

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.SourceColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.SourceColumns.New("Ticker/Cusip", 1)

                        oColumn.Name = "Ticker/Cusip"

                        oColumn.Ordinal = 1

                        oColumn.Flags = 102

                        oColumn.Size = 255

                        oColumn.datatype = 130

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.SourceColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.SourceColumns.New("Ticker", 2)

                        oColumn.Name = "Ticker"

                        oColumn.Ordinal = 2

                        oColumn.Flags = 102

                        oColumn.Size = 255

                        oColumn.datatype = 130

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.SourceColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.SourceColumns.New("Px_Mtd_Close", 3)

                        oColumn.Name = "Px_Mtd_Close"

                        oColumn.Ordinal = 3

                        oColumn.Flags = 118

                        oColumn.Size = 0

                        oColumn.datatype = 5

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.SourceColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.SourceColumns.New("Unit_value", 4)

                        oColumn.Name = "Unit_value"

                        oColumn.Ordinal = 4

                        oColumn.Flags = 118

                        oColumn.Size = 0

                        oColumn.datatype = 5

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.SourceColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.DestinationColumns.New("Statement_Date", 1)

                        oColumn.Name = "Statement_Date"

                        oColumn.Ordinal = 1

                        oColumn.Flags = 24

                        oColumn.Size = 0

                        oColumn.datatype = 135

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = False

                        

                oTransformation.DestinationColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.DestinationColumns.New("Cusip", 2)

                        oColumn.Name = "Cusip"

                        oColumn.Ordinal = 2

                        oColumn.Flags = 8

                        oColumn.Size = 9

                        oColumn.datatype = 129

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = False

                        

                oTransformation.DestinationColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.DestinationColumns.New("Ticker", 3)

                        oColumn.Name = "Ticker"

                        oColumn.Ordinal = 3

                        oColumn.Flags = 104

                        oColumn.Size = 50

                        oColumn.datatype = 129

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.DestinationColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.DestinationColumns.New("Price", 4)

                        oColumn.Name = "Price"

                        oColumn.Ordinal = 4

                        oColumn.Flags = 120

                        oColumn.Size = 0

                        oColumn.datatype = 5

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.DestinationColumns.Add oColumn

                Set oColumn = Nothing
 

                Set oColumn = oTransformation.DestinationColumns.New("UnitValue", 5)

                        oColumn.Name = "UnitValue"

                        oColumn.Ordinal = 5

                        oColumn.Flags = 120

                        oColumn.Size = 0

                        oColumn.datatype = 5

                        oColumn.Precision = 0

                        oColumn.NumericScale = 0

                        oColumn.Nullable = True

                        

                oTransformation.DestinationColumns.Add oColumn

                Set oColumn = Nothing
 

        Set oTransProps = oTransformation.TransformServerProperties
 

                

        Set oTransProps = Nothing
 

        oCustomTask1.Transformations.Add oTransformation

        Set oTransformation = Nothing
 

End Sub

Open in new window

0
Comment
Question by:gigglick
  • 2
3 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 21745763
0
 
LVL 5

Author Comment

by:gigglick
ID: 21745867
None of those sites had anything related to what I asked.
0
 
LVL 5

Accepted Solution

by:
gigglick earned 0 total points
ID: 21760700
If anyone comes by this question and needs help:  The data connections are set up different for Office 2007. If you have a dts package similar to above you'll need to change connection to use the Microsoft.ACE.OLEDB.12.0 driver.  Ex. code for connection1:

Set oConnection = goPackage.Connections.New("Microsoft.ACE.OLEDB.12.0")

        oConnection.ConnectionProperties("User ID") = "Admin"
        oConnection.ConnectionProperties("Data Source") = "C:\MyExcelFile.xlsm"
        oConnection.ConnectionProperties("Window Handle") = 0
        oConnection.ConnectionProperties("Mode") = 16
        oConnection.ConnectionProperties("Prompt") = 4
        oConnection.ConnectionProperties("Extended Properties") = "Excel 12.0"
        oConnection.ConnectionProperties("Locale Identifier") = 1033
        oConnection.ConnectionProperties("Jet OLEDB:Engine Type") = 0
        oConnection.ConnectionProperties("Jet OLEDB:Database Locking Mode") = 1
        oConnection.ConnectionProperties("Jet OLEDB:Global Partial Bulk Ops") = 2
        oConnection.ConnectionProperties("Jet OLEDB:Global Bulk Transactions") = 1
        oConnection.ConnectionProperties("Jet OLEDB:Create System Database") = False
        oConnection.ConnectionProperties("Jet OLEDB:Encrypt Database") = False
        oConnection.ConnectionProperties("Jet OLEDB:Don't Copy Locale on Compact") = False
        oConnection.ConnectionProperties("Jet OLEDB:Compact Without Replica Repair") = False
        oConnection.ConnectionProperties("Jet OLEDB:SFP") = False
        oConnection.ConnectionProperties("Jet OLEDB:Support Complex Data") = False
       
        oConnection.Name = "Connection 1"
        oConnection.ID = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "C:\MyExcelFilet.xlsm"
        oConnection.UserID = "Admin"
        oConnection.ConnectionTimeout = 60
        oConnection.UseTrustedConnection = False
        oConnection.UseDSL = False
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now