DTS Error

I need to copy some datas from Oracle to MS Access db. To do it, I use SQL Server Import and Export Data tool. But I want to move it to a Visual Basic program. I saved DTS package from Import and Export tool. I embedded to my VB program. But sometimes it gives me error like:

XXX Step failed
Microsoft Data Transformation Services (DTS) Data Pump
The number of failing rows exceeds the maximum specified. (Microsoft JET Database Engine (80004005):
Could not update; currently locked by user 'Admin' on machine 'X'.)

How can I solve the problem?
My connections strings are;

For Oracle :
Set oConnection = goPackage.Connections.New("OraOLEDB.Oracle")

oConnection.ConnectionProperties("Persist Security Info") = False
oConnection.ConnectionProperties("User ID") = user
oConnection.ConnectionProperties("Data Source") = db
oConnection.ConnectionProperties("Window Handle") = 0
oConnection.ConnectionProperties("Locale Identifier") = 1055
oConnection.ConnectionProperties("Prompt") = 2
oConnection.ConnectionProperties("OLE DB Services") = -1

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = db
oConnection.UserID = user
oConnection.password = password
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

For Access:
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")

oConnection.ConnectionProperties("Data Source") = " & download_form.destination_text.text & "
oConnection.ConnectionProperties("Mode") = 3


oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = download_form.destination_text.Text
oConnection.ConnectionTimeout = 160
oConnection.UseTrustedConnection = False
oConnection.UseDSL = FalseDepending on what version of SQL Server you are running you can create a graphical DTS package instead of just using the import export tool...

I believe if you are running above SQL v. 7 it has the capabilites of the actual dts...

Underneath your console root you should have access to the Data Transformation Services folder... when the node is opened, click once on local packages, then right click and design new package... When you create your dts package through this and use the transform data task from the oracle db connection to the access db, you are then going to want to save the package... You can save it in VB format. To do this under the Location drop down in the save box, select vb file and the directory where you want to save it...|||We use sql server 2000. I created package. I have no problem about creating package. I think the problem is parameters of connection or driver of access connection. Which parameter determines the max. rows which are inserted ?

Originally posted by justastef
Depending on what version of SQL Server you are running you can create a graphical DTS package instead of just using the import export tool...

I believe if you are running above SQL v. 7 it has the capabilites of the actual dts...

Underneath your console root you should have access to the Data Transformation Services folder... when the node is opened, click once on local packages, then right click and design new package... When you create your dts package through this and use the transform data task from the oracle db connection to the access db, you are then going to want to save the package... You can save it in VB format. To do this under the Location drop down in the save box, select vb file and the directory where you want to save it...|||I'm sorry I can't be more of help however, I did ask a friend for you and he suggested to try these few things

Increase the connection timeout to a higher number (try 120 or above) and make sure you have the latest service pack installed for SQL Server.sql

DTS Classes Not Showing in Visual Studio

I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.

I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.

What gives with only having Microsoft.SqlServer.Server available?

thanks,

Scott

Did you install SSIS?|||

Yes I did a full install of both vs and sql (including books and samples).

Some more info:

I'm trying to do this from an asp.net web service project. Do I need to add the assemblies to web.config? If so, does anyone have the assembly details (PublicKeyToken, etc)?

thanks--Scott

|||

But did you specifically install Integration Services? You can have the development tools without having all of SSIS.

However there's no reason I can think of that the Add Reference dialog in Visual Studio wouldn't show a long list of Microsoft.SqlServer... assemblies, unless they weren't there. You may need to tweak Web permissions later to use them successfully in the deployed app, but you should at least see them.

-Doug

|||You need to reference SSIS assemblies, like Microsoft.SQLServer.ManagedDTS.dll.
They are by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.|||

Doug,

Thanks for the reply. Yes, SSIS is installed. I had originally installed sql tools and books online. After doing a number or reinstalls of the tools and VS (rebooting here and there) I installed sql server dev ed (including the database engine, SSRS, SSIS, SSNS). I then set the services to manual (to not bog my machine down).

|||

Michael,

Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server.

If I add a reference to web.config like:

<add assembly="Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

I receive an error, "Could not load assembly...<listed above>...system cannot find the file specified.

If I have a full install of sql server (all services installed locally), why can't my system find the assembly?

thanks again for your help

|||The assembly name is Microsoft.SqlServer.ManagedDTS, not Microsoft.SqlServer.Dts.Runtime (which is one of the namespaces defined in this assembly).|||

Ah, I was not aware of that. I added the following to my web.config and it works fine.

thanks for your help.

<add assembly="Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

sql

DTS Classes Not Showing in Visual Studio

I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.

I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.

What gives with only having Microsoft.SqlServer.Server available?

thanks,

Scott

Did you install SSIS?|||

Yes I did a full install of both vs and sql (including books and samples).

Some more info:

I'm trying to do this from an asp.net web service project. Do I need to add the assemblies to web.config? If so, does anyone have the assembly details (PublicKeyToken, etc)?

thanks--Scott

|||

But did you specifically install Integration Services? You can have the development tools without having all of SSIS.

However there's no reason I can think of that the Add Reference dialog in Visual Studio wouldn't show a long list of Microsoft.SqlServer... assemblies, unless they weren't there. You may need to tweak Web permissions later to use them successfully in the deployed app, but you should at least see them.

-Doug

|||You need to reference SSIS assemblies, like Microsoft.SQLServer.ManagedDTS.dll.
They are by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.|||

Doug,

Thanks for the reply. Yes, SSIS is installed. I had originally installed sql tools and books online. After doing a number or reinstalls of the tools and VS (rebooting here and there) I installed sql server dev ed (including the database engine, SSRS, SSIS, SSNS). I then set the services to manual (to not bog my machine down).

|||

Michael,

Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server.

If I add a reference to web.config like:

<add assembly="Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

I receive an error, "Could not load assembly...<listed above>...system cannot find the file specified.

If I have a full install of sql server (all services installed locally), why can't my system find the assembly?

thanks again for your help

|||The assembly name is Microsoft.SqlServer.ManagedDTS, not Microsoft.SqlServer.Dts.Runtime (which is one of the namespaces defined in this assembly).|||

Ah, I was not aware of that. I added the following to my web.config and it works fine.

thanks for your help.

<add assembly="Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

DTS activeX script...please help begginner

Hi there, im using this vb script.

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("SES_STATUS") = DTSSource("Session Status")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_DATE") = DTSSource("Start Date")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
DTSDestination("SES_ID") = DTSSource("Start Date")
Main = DTSTransformstat_InsertQuery
End Function

what I would Like to know is how can I complete this script so that it knows when to run an update statement and when to run a insert statement (I have already created the update and insert statements) as this dts package will run every night and the source database tables are updated and inserted into quite regualar. So something like this is needed:

if record exists in destination
do update statement

if record not exist
do insert startement

many thanks in advance.

You can use an execute SQL task with two statements, one that inserts if the record does not exist and one that updates if the record does exist.

-Sue

|||Thanks for your reply, could you please give me a code example of this.

Many thanks|||

thanks for that. I've abandoned the activeX and have decided to use an execute SQL task using this tsql:

INSERT INTO dbo.Target
SELECT *
FROM dbo.Source AS s
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Target AS t
WHERE t.title_id = s.title_id
)

however my source and target tables are on different database, so when I choose a connection in the execute sql task I get an error as each database table exists different database, how could I over come this?

many thanks

|||

Im using this activeX vb script to perform updates or inserts depending if the primary key already exists in the destination table, however the it never updates when it should, infact it never updates it always inserts, which leads to a violation of the primary key. Can somebody help me see where Im going wrong...thanks

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
Dim sSessCode
sSessCode = DTSDestination("SES_ID")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
If DTSSource("Start Date") = "01/01/1753" Then
DTSDestination("SES_DATE") = "01/01/1980"
Else
DTSDestination("SES_DATE") = Cdate(DTSSource("Start Date"))
End If
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SLC_ID") = DTSSource("Sub Location")

DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("SES_STATUS") = DTSSource("Session Status")

Select Case sSessCode
Case DTSSource("Session Code")
' MsgBox("Update")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_UpdateQuery
Case Else
' MsgBox("Insert")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_InsertQuery
End Select
End Function

DTS "general error" when trying to export/transform to txt file

Hi there. I'm using the DTS Import/Export wizard to attempt to export data to a text file. I am using the visual basic transformations (or whatever they're called) to change column names at the destination, but that's about the most unusual or complex thing I am doing.

When I finish up, I try to save the export for later use, in the source server's Meta Data Services. It starts to save and then craps out with the following very useless error:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: General error -2147217355 (80041035)

Google turns up nothing on those number strings... anyone have any ideas, or failing that, a pointer to a tutorial page on how to create a data export script that includes the flexibility to change column names? Maybe I'm doing something wrong and don't realize it.www.sqldts.com is a good site to take a look. There might be something there.

DTS - SQL SERVER

Does the possibility exist of executing a DTS created in SQL Server 7
through a Query using a page ASP or Visual Basic?Hi Frank.
DTS has a COM API, so you can invoke packages (& even create / edit
packages) via the COM interfaces.
You can find examples of how to do this in VB here:
http://www.sqldts.com/default.aspx?208
Or in T-SQL here:
http://www.databasejournal.com/features/mssql/article.php/1459181
HTH
Regards,
Greg Linwood
SQL Server MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Oj4MW3bnDHA.1656@.tk2msftngp13.phx.gbl...
>
> Does the possibility exist of executing a DTS created in SQL Server 7
> through a Query using a page ASP or Visual Basic?
>
>

DTS

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

Dts

Hi,
I have a dts package that I created which uses an odbc connection to go and
copy a dbf file using visual fox pro drivers to a sql server database. The p
ackage runs fine when I execute it manually. But whenever I try to use the s
ql server agent(schedule) to run it as a job, it doesn't work. I realy dont
know what to do...
Thanks for the helpCan anyone help me ?

dt_addtosourcecontrol

In the master database is a stored procedure named dt_addtosourcecontrol.
Does this imply that if you have Visual Source Safe, there is a slick way of
quickly checking files in and out or similar without using VSS? Thanks in
advance.
Mark--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
These are created by the Visual Studio tools but yes I have seen some
scripts that allow you to use them from QA, try searching google.
I'd suggest you take a look at mssqlXpress from
http://www.xpressapps.com/ a nice QA replacement with VSS
integration.
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:ePeHGAokDHA.2432@.TK2MSFTNGP10.phx.gbl...
> In the master database is a stored procedure named
> dt_addtosourcecontrol. Does this imply that if you have Visual
> Source Safe, there is a slick way of quickly checking files in and
> out or similar without using VSS? Thanks in advance.
> Mark
>
--BEGIN PGP SIGNATURE--
Version: PGP 8.0
iQA/AwUBP40IFlOULLw5tFTpEQIfWgCgppxFmwNPI3a7iFGZHkeYd9mTQvEAoIQl
MSRdisM+fik3DPtKwYVBWLYt
=GZKR
--END PGP SIGNATURE--