Solved

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

Posted on 2008-06-09
3
668 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

806 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