dts import agent: saved

I created and saved a DTS package that appends the records from an access
table into a SQL table. Each time I run the DTS package, SQL appends all
records from the access table, leaving duplicate records.
How can I change a DTS package to just import/append only new records from
the access table?I think there are two options here:
1) use 'query' to get your data out rather than using 'copy table'
option. a primary column is handy in this case, e.g. make the access
database as a linked server, then use a join in the query to only get
new records.
2) rebuild the table each time (delete all the records and get the all
data).
Mel|||Can you give me just a sample of code that selects data from a linked server
and inserts it into a sql table?
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145132520.664664.176650@.e56g2000cwe.googlegroups.com...
>I think there are two options here:
> 1) use 'query' to get your data out rather than using 'copy table'
> option. a primary column is handy in this case, e.g. make the access
> database as a linked server, then use a join in the query to only get
> new records.
> 2) rebuild the table each time (delete all the records and get the all
> data).
> Mel
>|||Other options would be to use the ActiveX options in the Transform step
of th data pump. You could compare Primary keys of the rows to be
inserted to the pre-existing rows, and then simply skip that row.
There are a couple of good books on DTS; I recommend Professional SQL
Server 2000 DTS (Amazon link here:
http://www.amazon.com/gp/product/B0...5Fencoding=UTF8
)
HTH,
Stu|||thanks
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1145149418.433259.318180@.g10g2000cwb.googlegroups.com...
> Other options would be to use the ActiveX options in the Transform step
> of th data pump. You could compare Primary keys of the rows to be
> inserted to the pre-existing rows, and then simply skip that row.
> There are a couple of good books on DTS; I recommend Professional SQL
> Server 2000 DTS (Amazon link here:
> http://www.amazon.com/gp/product/B0...5Fencoding=UTF8
> )
> HTH,
> Stu
>|||As requested,
select id, column2, column3
from AccessTable
where id not in
(select id from SQLTable)
id = primary key
The above query will only select (then insert) records that are not yet
in the SQL Table.
Mel|||how can your code insert data without the "INSERT INTO" syntax?
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145218127.199907.322650@.t31g2000cwb.googlegroups.com...
> As requested,
> select id, column2, column3
> from AccessTable
> where id not in
> (select id from SQLTable)
> id = primary key
> The above query will only select (then insert) records that are not yet
> in the SQL Table.
> Mel
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

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

Monday, March 19, 2012

DTS Append Records

Sunday, February 26, 2012

DTS

Sunday, February 19, 2012

dtexec

dts import agent

Blog Archive

About Me