gigglick
asked on
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
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
ASKER
None of those sites had anything related to what I asked.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.themssforum.com/SQLServer/SCREWY-LAST/
http://www.whitebinder.com/filter/1686/s/sql-dts.aspx
http://developer-news.blogspot.com/2008/04/sql-server-table-types.html
http://www.ebooksbay.org/KnowFree/2007/05/
http://www.1001annonces.com/1001newsgroups/lmess.php?name=microsoft.public.sqlserver.dts