Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

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

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
gigglick
Asked:
gigglick
  • 2
1 Solution
 
gigglickAuthor Commented:
None of those sites had anything related to what I asked.
0
 
gigglickAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now