I have created a Data Transformation Service (DTS) in SQL Server using the import/export data wizard, saved it as a Visual Basic file and then upgraded it to Vb.Net.
I am importing data from an excel file into a SQL Server table.
The basics are working fine, but I now want to improve the importing function.
I want to disallow duplicate entries so that the same data cannot be entered more than once - except for if the data for a specific record has changed - and in this case I want to add the record, and move its original value to another table.
Does anyone have an idea how I can go about this?/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
idname
1a
2b
3c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
http://www.sqldts.com/default.aspx?271
Try both the above code and the url for options, to disallow duplicates make your table UNIQUE INDEX compatible because SQL Server will not allow the creation of UNIQUE INDEX on Columns that already include duplicate values. Include IGNORE_DUP_KEY in your create INDEX statement. Hope this helps
Kind regards,
Gift Peddie|||I am not sure how to implement this code within the code I already have.
I want to put this code behind the click event of a button in a .aspx.vb file.
How do I implement what you suggested within my code?
Option Strict Off
Option Explicit On
Module TestingOrders
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: H:\TestingOrders.bas
'Package Name: TestingOrders
'Package Description: DTS package description
'Generated Date: 18/01/2005
'Generated Time: 11:45:36
'****************************************************************Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public Sub Main()
goPackage = goPackageOld
goPackage.Name = "TestingOrders"
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.Connection2
'---- a new connection defined below.
'For security purposes, the password is never scripted
oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
'File name here equal to user's choice of file
oConnection.ConnectionProperties.Item("Data Source").Value = "H:\Orders.xls"
oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;"
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "H:\Orders.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>"
'UPGRADE_WARNING: Couldn't resolve default property of object oConnection. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Connections.Add(oConnection)
'---- a new connection defined below.
'For security purposes, the password is never scripted
oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "101032844"
oConnection.ConnectionProperties.Item("Data Source").Value = "INDUS"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "INDUS"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "101032844"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
'UPGRADE_WARNING: Couldn't resolve default property of object oConnection. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Connections.Add(oConnection)
'-----------------------
' create package steps information
'-----------------------
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'---- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Step"
oStep.Description = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] 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
oStep.FailPackageOnError = False
'UPGRADE_WARNING: Couldn't resolve default property of object oStep. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Steps.Add(oStep)
'-----------------------
' create package tasks information
'-----------------------
'---- call Task_Sub1 for task Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task (Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task)
Call Task_Sub1(goPackage)
'-----------------------
' Save or execute package
'-----------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute()
'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Steps.Item. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
tracePackageError(goPackage)
goPackage.Uninitialize()
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
End Sub
'------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'------------------------
Public Sub tracePackageError(ByRef oPackage As DTS.Package)
Dim ErrorCode As Integer
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Integer
Dim ErrorIDofInterfaceWithError As String
Dim i As Short
For i = 1 To oPackage.Steps.Count
If oPackage.Steps.Item(i).ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
oPackage.Steps.Item(i).GetExecutionErrorInfo(ErrorCode, ErrorSource, ErrorDescription, ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError)
MsgBox(oPackage.Steps.Item(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription)
End If
Next i
End Sub
'---- define Task_Sub1 for task Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task (Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task)
Public Sub Task_Sub1(ByVal goPackage As DTS.Package2)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.DataPumpTask2
'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task"
oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task"
oCustomTask1.Description = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task"
oCustomTask1.SourceConnectionID = 1
'SQL Statement here
'Eliminate duplicates here
'Create a test table to load the data into
'Create a duplicate/archive table to move duplicates to from original table
oCustomTask1.SourceSQLStatement = "select `Order No`,`Country`,`Desc`,`Amount` from `Sheet1$` WHERE `Order No` = 1 "
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "[101032844].[101032844].[TestingOrders]"
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
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0
Call oCustomTask1_Trans_Sub1(oCustomTask1)
'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Tasks.Add(oTask)
End Sub
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As DTS.DataPumpTask2)
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
'UPGRADE_WARNING: Couldn't resolve default property of object oCustomTask1.Transformations. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
oColumn = oTransformation.SourceColumns.New("Order No", 1)
oColumn.Name = "Order No"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add(oColumn)
oColumn = oTransformation.SourceColumns.New("Country", 2)
oColumn.Name = "Country"
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)
oColumn = oTransformation.SourceColumns.New("Desc", 3)
oColumn.Name = "Desc"
oColumn.Ordinal = 3
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add(oColumn)
oColumn = oTransformation.SourceColumns.New("Amount", 4)
oColumn.Name = "Amount"
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)
oColumn = oTransformation.DestinationColumns.New("Order_Number", 1)
oColumn.Name = "Order_Number"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add(oColumn)
oColumn = oTransformation.DestinationColumns.New("Country", 2)
oColumn.Name = "Country"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add(oColumn)
oColumn = oTransformation.DestinationColumns.New("Description", 3)
oColumn.Name = "Description"
oColumn.Ordinal = 3
oColumn.Flags = 120
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add(oColumn)
oColumn = oTransformation.DestinationColumns.New("Amount", 4)
oColumn.Name = "Amount"
oColumn.Ordinal = 4
oColumn.Flags = 120
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add(oColumn)
oTransProps = oTransformation.TransformServerProperties
'UPGRADE_WARNING: Couldn't resolve default property of object oCustomTask1.Transformations. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
oCustomTask1.Transformations.Add(oTransformation)
End Sub
End Module
|||I don't write VB but I think you are using the code to eliminate duplicates, the UNIQUE index will do it if the IGNORE_DUPLICATES is added to the create index statement. The link below has a working DTS Package with code in VB6 you can consume it as COM object by adding Reference to it in your code. The linked server code can be called as stored proc. I also found very detailed tutorial using Excel object Model by Ken Getz on MSDN, he is a member of the International .NET user groups speakers. Hope this helps.
http://www.sqldts.com/default.aspx?t=6&s=101&i=243&p=1&a=0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp
Kind regards,
Gift Peddie
1Dave wrote: