Solved

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

Posted on 2008-06-09
3
675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help needed in sql query 4 26
Many to one in one row 2 35
SQL- GROUP BY 4 21
8 hour Continual coverage based on Time in and Time Out 9 17
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

735 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