DTS execution account

hi all,

I hace a DTS package that contains a transformation task and an ActiveX Script Task (the last task accesses the registry in order to read some values). So first, does anybody knows under what user account the DTS package will run? and second what permissions should the account have in order to execute the DTS?.

Thanks in advance.

God Bless.The answer is that it depends (you knew I was going to say that).

If it is run interactively, it will run under the login of whoever is logged in (it will also run in the client context of your login, so if you are using EM from a client workstation and are not using it through Terminal Services, watch out!)

If it is run via a SQL Server job, then it will run in the context of the SQL Agent Service.

If you schedule it using NT Scheduled tasks, you can specify the user when setting up the task.

If ou execute it from an SP, I think you can specify the user context (though I don't swear to that -- it might pick up the user context of the person executing the SP).

Originally posted by mvargasp
hi all,

I hace a DTS package that contains a transformation task and an ActiveX Script Task (the last task accesses the registry in order to read some values). So first, does anybody knows under what user account the DTS package will run? and second what permissions should the account have in order to execute the DTS?.

Thanks in advance.

God Bless.

DTS Copy locks web

I built a simple DTS job to copy the contents of a table in a SQL Server
2000 database from one server to another.
The transformation is a simple column copy from source table to destination
table.
I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
Insert and Always Commit Final Batch (inerst batch size=5000).
The source table has about 0.5 million records
As soon as I start the DTS job, any web page with a database connection
freezes up (i.e. you cannnot load it into a browser)
I do not have the Table Lock option set in the DTS package.
Does anyone have any ideas why a copy package would lock up the application
like this?
On Sat, 11 Aug 2007 08:44:51 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>I built a simple DTS job to copy the contents of a table in a SQL Server
>2000 database from one server to another.
>The transformation is a simple column copy from source table to destination
>table.
>I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
>Insert and Always Commit Final Batch (inerst batch size=5000).
>The source table has about 0.5 million records
>As soon as I start the DTS job, any web page with a database connection
>freezes up (i.e. you cannnot load it into a browser)
>I do not have the Table Lock option set in the DTS package.
>Does anyone have any ideas why a copy package would lock up the application
>like this?
Is the DTS package copying from or to the same table the pages are
accessing?
J.
|||It copies from the table that the web page is accesssing.
But a copy is just a read.
Why would this apparently lock the table?
|||On Sat, 11 Aug 2007 18:35:05 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>It copies from the table that the web page is accesssing.
>But a copy is just a read.
>Why would this apparently lock the table?
Because the web page connection has an isolation level of repeatable
read or serializable. Yes, it's excessive caution, but I suspect it's
the answer.
J.
|||Hi Dave,
I understand that when you executed your SQL 2000 DTS package in parallel
with your web application, all the web pages that used a database
connection froze up.
If I have misunderstood, please let me know.
From the issue appearance, it seemed that a deadlock was caused. To narrow
down this issue, I would like to collect more information from you:
1.What is the transaction isolation level of your web application?
2.What is the transaction isolation level of your DTS package?
Open your DTS package in Enterprise Manager, right click the panel in DTS
package designer, select "Package Properties", switch to the Advanced tab,
check the Transaction isolation level.
3. Refer to this article to enable the trace flag T1204 and T3605 so that
you can get more information in the error log.
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/en-us
After the above operation, run your DTS package again to reproduce your
issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
error logs for further research.
By default, the SQL error logs are located in the folder
%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
Look forward to your response and we are glad to work with you for further
research.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Thanks Charles.
The DTS package resides on the local server. I do not see any errors in
either the ERRORLOG or SQLAGENT files.
I uncheck the "Use Transactions" box in the DTS package properties but I
still get the app locking up until I cancel the DTS operation.
Can you tell me where I check for the transation isolation level on thec web
server?
Thanks
Dave
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:0OayAjW3HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Dave,
> I understand that when you executed your SQL 2000 DTS package in parallel
> with your web application, all the web pages that used a database
> connection froze up.
> If I have misunderstood, please let me know.
> From the issue appearance, it seemed that a deadlock was caused. To narrow
> down this issue, I would like to collect more information from you:
> 1. What is the transaction isolation level of your web application?
> 2. What is the transaction isolation level of your DTS package?
> Open your DTS package in Enterprise Manager, right click the panel in DTS
> package designer, select "Package Properties", switch to the Advanced tab,
> check the Transaction isolation level.
> 3. Refer to this article to enable the trace flag T1204 and T3605 so that
> you can get more information in the error log.
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/en-us
> After the above operation, run your DTS package again to reproduce your
> issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
> error logs for further research.
> By default, the SQL error logs are located in the folder
> %ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
> Look forward to your response and we are glad to work with you for further
> research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>

DTS Copy locks web

I built a simple DTS job to copy the contents of a table in a SQL Server
2000 database from one server to another.
The transformation is a simple column copy from source table to destination
table.
I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
Insert and Always Commit Final Batch (inerst batch size=5000).
The source table has about 0.5 million records
As soon as I start the DTS job, any web page with a database connection
freezes up (i.e. you cannnot load it into a browser)
I do not have the Table Lock option set in the DTS package.
Does anyone have any ideas why a copy package would lock up the application
like this?On Sat, 11 Aug 2007 08:44:51 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>I built a simple DTS job to copy the contents of a table in a SQL Server
>2000 database from one server to another.
>The transformation is a simple column copy from source table to destination
>table.
>I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identit
y
>Insert and Always Commit Final Batch (inerst batch size=5000).
>The source table has about 0.5 million records
>As soon as I start the DTS job, any web page with a database connection
>freezes up (i.e. you cannnot load it into a browser)
>I do not have the Table Lock option set in the DTS package.
>Does anyone have any ideas why a copy package would lock up the application
>like this?
Is the DTS package copying from or to the same table the pages are
accessing?
J.|||It copies from the table that the web page is accesssing.
But a copy is just a read.
Why would this apparently lock the table?|||On Sat, 11 Aug 2007 18:35:05 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>It copies from the table that the web page is accesssing.
>But a copy is just a read.
>Why would this apparently lock the table?
Because the web page connection has an isolation level of repeatable
read or serializable. Yes, it's excessive caution, but I suspect it's
the answer.
J.|||Hi Dave,
I understand that when you executed your SQL 2000 DTS package in parallel
with your web application, all the web pages that used a database
connection froze up.
If I have misunderstood, please let me know.
From the issue appearance, it seemed that a deadlock was caused. To narrow
down this issue, I would like to collect more information from you:
1. What is the transaction isolation level of your web application?
2. What is the transaction isolation level of your DTS package?
Open your DTS package in Enterprise Manager, right click the panel in DTS
package designer, select "Package Properties", switch to the Advanced tab,
check the Transaction isolation level.
3. Refer to this article to enable the trace flag T1204 and T3605 so that
you can get more information in the error log.
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/en-us
After the above operation, run your DTS package again to reproduce your
issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
error logs for further research.
By default, the SQL error logs are located in the folder
%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
Look forward to your response and we are glad to work with you for further
research.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks Charles.
The DTS package resides on the local server. I do not see any errors in
either the ERRORLOG or SQLAGENT files.
I uncheck the "Use Transactions" box in the DTS package properties but I
still get the app locking up until I cancel the DTS operation.
Can you tell me where I check for the transation isolation level on thec web
server?
Thanks
Dave
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:0OayAjW3HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Dave,
> I understand that when you executed your SQL 2000 DTS package in parallel
> with your web application, all the web pages that used a database
> connection froze up.
> If I have misunderstood, please let me know.
> From the issue appearance, it seemed that a deadlock was caused. To narrow
> down this issue, I would like to collect more information from you:
> 1. What is the transaction isolation level of your web application?
> 2. What is the transaction isolation level of your DTS package?
> Open your DTS package in Enterprise Manager, right click the panel in DTS
> package designer, select "Package Properties", switch to the Advanced tab,
> check the Transaction isolation level.
> 3. Refer to this article to enable the trace flag T1204 and T3605 so that
> you can get more information in the error log.
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/en-us
> After the above operation, run your DTS package again to reproduce your
> issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
> error logs for further research.
> By default, the SQL error logs are located in the folder
> %ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
> Look forward to your response and we are glad to work with you for further
> research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Dave,
The transaction isolation level of your web application depends on your
coding. If you did not set transaction isolation level explicitly, by
default, it was READ COMMITTED.
Since there was no deadlock information in the error log, I think that this
issue should be related to lock escalation. I noticed that your source
table had about 0.5 million records and your DTS inserts batch size was
5000, it is very common that a large batch insert or update will cause
table level lock which is incompatible with shared lock. In this case, your
application could not read any data from the table until the bulk
insert/update finished.
I recommend that you schedule a time to run your DTS job when your web
application is not busy or has no workload.
You may also consider transfer the data incrementally, each time
transferring a few records to the destination table.
The methods can ensure that there is little impact to your application
performance.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

DTS Copy locks web

I built a simple DTS job to copy the contents of a table in a SQL Server
2000 database from one server to another.
The transformation is a simple column copy from source table to destination
table.
I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
Insert and Always Commit Final Batch (inerst batch size=5000).
The source table has about 0.5 million records
As soon as I start the DTS job, any web page with a database connection
freezes up (i.e. you cannnot load it into a browser)
I do not have the Table Lock option set in the DTS package.
Does anyone have any ideas why a copy package would lock up the application
like this?On Sat, 11 Aug 2007 08:44:51 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:
>I built a simple DTS job to copy the contents of a table in a SQL Server
>2000 database from one server to another.
>The transformation is a simple column copy from source table to destination
>table.
>I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
>Insert and Always Commit Final Batch (inerst batch size=5000).
>The source table has about 0.5 million records
>As soon as I start the DTS job, any web page with a database connection
>freezes up (i.e. you cannnot load it into a browser)
>I do not have the Table Lock option set in the DTS package.
>Does anyone have any ideas why a copy package would lock up the application
>like this?
Is the DTS package copying from or to the same table the pages are
accessing?
J.|||It copies from the table that the web page is accesssing.
But a copy is just a read.
Why would this apparently lock the table?|||On Sat, 11 Aug 2007 18:35:05 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:
>It copies from the table that the web page is accesssing.
>But a copy is just a read.
>Why would this apparently lock the table?
Because the web page connection has an isolation level of repeatable
read or serializable. Yes, it's excessive caution, but I suspect it's
the answer.
J.|||Hi Dave,
I understand that when you executed your SQL 2000 DTS package in parallel
with your web application, all the web pages that used a database
connection froze up.
If I have misunderstood, please let me know.
From the issue appearance, it seemed that a deadlock was caused. To narrow
down this issue, I would like to collect more information from you:
1. What is the transaction isolation level of your web application?
2. What is the transaction isolation level of your DTS package?
Open your DTS package in Enterprise Manager, right click the panel in DTS
package designer, select "Package Properties", switch to the Advanced tab,
check the Transaction isolation level.
3. Refer to this article to enable the trace flag T1204 and T3605 so that
you can get more information in the error log.
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/en-us
After the above operation, run your DTS package again to reproduce your
issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
error logs for further research.
By default, the SQL error logs are located in the folder
%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
Look forward to your response and we are glad to work with you for further
research.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks Charles.
The DTS package resides on the local server. I do not see any errors in
either the ERRORLOG or SQLAGENT files.
I uncheck the "Use Transactions" box in the DTS package properties but I
still get the app locking up until I cancel the DTS operation.
Can you tell me where I check for the transation isolation level on thec web
server?
Thanks
Dave
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:0OayAjW3HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Dave,
> I understand that when you executed your SQL 2000 DTS package in parallel
> with your web application, all the web pages that used a database
> connection froze up.
> If I have misunderstood, please let me know.
> From the issue appearance, it seemed that a deadlock was caused. To narrow
> down this issue, I would like to collect more information from you:
> 1. What is the transaction isolation level of your web application?
> 2. What is the transaction isolation level of your DTS package?
> Open your DTS package in Enterprise Manager, right click the panel in DTS
> package designer, select "Package Properties", switch to the Advanced tab,
> check the Transaction isolation level.
> 3. Refer to this article to enable the trace flag T1204 and T3605 so that
> you can get more information in the error log.
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/en-us
> After the above operation, run your DTS package again to reproduce your
> issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
> error logs for further research.
> By default, the SQL error logs are located in the folder
> %ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
> Look forward to your response and we are glad to work with you for further
> research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Dave,
The transaction isolation level of your web application depends on your
coding. If you did not set transaction isolation level explicitly, by
default, it was READ COMMITTED.
Since there was no deadlock information in the error log, I think that this
issue should be related to lock escalation. I noticed that your source
table had about 0.5 million records and your DTS inserts batch size was
5000, it is very common that a large batch insert or update will cause
table level lock which is incompatible with shared lock. In this case, your
application could not read any data from the table until the bulk
insert/update finished.
I recommend that you schedule a time to run your DTS job when your web
application is not busy or has no workload.
You may also consider transfer the data incrementally, each time
transferring a few records to the destination table.
The methods can ensure that there is little impact to your application
performance.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Dave,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

DTS connection

Hi
I'm using DTS to transform Data between 2 SQL server .My package uses Activex transformation with lookups.first I used 2 connections one for the Source and another for the destination and I used the destination connection to fetch the data for thr lookup but while executing I faced an error saying Connection is busy with results from another command. I creaed another connection for the lookups and it worked. I started the profiler while the package was running and I noticed the the lookup connection is opend and closed once per each lookup (in my case I use up to for lookups per row) which leaks my performance down .so i have these questions which I hope any one thankfully answer:
1- why can't I use the Destination connection for the lookup?
2- why the connection is open and closed each time it looks-up?

Thanks in advanceHi Eisa,

This is a forum for the next version of DTS, called Integration Services, so you probably won't get enough qualified eye balls looking at your question to answer it.
Try the microsoft.public.sqlserver.dts newsgroup instead, that should have more activity on it.

thanks,
ashsql

DTS connecting to CA Datacom

I am trying to import data from a CA Datacom database on a mainframe using SQL Server2000 Data Transformation Services. I can sign in with my username and password and see all the CA Datacom tables listed in DTS. When I select a table I get an error:

"Your provider does not support all the interfaces/methods required by DTS."

I am using the ODBC driver: CA Datacom/DB version 78.73.7970.

I have read in some other posts on the internet of people using this ODBC driver and being able to import data into SQL Server. Anybody have any ideas? Anybody doing this successfully?

your input is greatly appreciated.Ok.. I figured it out. For anyone else that may ever try doing this:

Instead of selecting the CA /Datacom DB driver in the pulldown list in DTS, I chose "Other ODBC data source" and this let me download tables.

DTS And VBScript

I have an application written in C#, which allows the user to load their destination schema (in xml format) and writer their transformation expressions either in VBScript or in T-SQL. It uses DTS to do the ETL job.

Everything works great except with one problem. If there is a hypen ("-") in any of the destination attributes, the transformation fails with syntax error. It happens only with VBScript.

Here is my transformation:

"Function Main()

DTSDestination(\"Field-1\") = #01/01/2004#

End Function"

If I replace the hypen with underscore, it works without any problem.

Does anyone have any idea about this problem?Did you try to put the field name between brackets?
[field-1]|||Tried with \"[Field-1]\" and it did not do the magic|||Perhaps a hyphen is an invalid character for a field name. Use underscores and your problem is solved. You shouldn't be using '-'s in field names, anyway.|||In general hypen ('-'s) are not allowed in a field. But if you enclose in [], it is a valid chr (atleast in SQLServer!).

As my application is a B2B application and schema is maintained by an organization, I don't have any control. Hence I can not modify the field name.

BTW, I tried the same this with Enterprise Manager, it works.

DTS and SQL Server

Is it possible to run Data Transformation Services (DTS) on demand by having a user click on a button in an application with SQl Server?
I am creating a project in vb.net where I want the user to be able to hit an "update" button and data will be updated to my SQL Server table by DTS.

Also is it possible to check for duplicate entries before running DTS? E.g I want to append to my table but exclude duplicate entries UNLESS the data has changed for that entry.>>Is it possible to run Data Transformation Services (DTS) on demand

Yes. http://msdn.microsoft.com/msdnmag/issues/04/01/WebQA/

>>Also is it possible to check for duplicate entries before running DTS

Make this a step in your dts package

DTS and Lotus Notes

I attempted to do a Transformation DataPump between SQL
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.You do know that a lotus notes "database" isa not a database. And since your working with a database, you must have some Lotus Notes "guru" who is pushing for this, why not let them use notrix or pump to get the data.

Deliver a comma delimted file to a location everyday...what? they want real time updates?

How do you do that to a non relational architecture?|||Originally posted by sch5479
I attempted to do a Transformation DataPump between SQL
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.

Why not have the Lotus Notes people setup the DESC sub-system within Lotus Domino and have the system directly access the information wither live or timed?

Frank|||While Notes Databases arn't relational don't see why that DTS has a problem as you can get Crystal report to run against notes data. Personally I wouldn't touch Notrix my preference has always been to get the Domino application to access the data it needs using LotusScript and ODBC which just makes SQL queries to the data.

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 (Oracle to SQL Server 2000)

I am trying to convert an application database from Oracle to SQL Server 2000. I am using Data Transformation Services utility provided by SQL Server. I am converting data on table-by-table basis. However, whenever I convert DATE fields (form Oracle) into DATETIME fields (to SQL Server 2000), my tasks fails. Does anyone knows the reason and, more important, a solution to this problem ?Hi

I'm also working with an Oracle Database.

In my configuration it works.
I used a Transform data task and the transformation I used is a copy column.

So

Oracle:
numeric/field1, datetime/field2 >> source in Transform data task

SQL Server 2000:
numeric/field1, datetime/field2 >> destination in Transform data task

Transformations: let SQL Server do automapping, copy column.

Greetings
Jon@.s

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
Thank You
Hi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You
|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
Thank YouHi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBB
B'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBB
B'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table bas
ed
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table bas
ed
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
--
Thank YouHi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:
> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:
> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS "execute on main package thread" issue

here is the error i get:

Microsoft Data Transformation Services (DTS) Package
0
(1:Replicate D318_DOCKET) SubStep 'DTSStep_DTSDataPumpTask_2' failed with the following error:
Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION

the package runs fine by itself from within Enterprise Manager but when i try to run the package from a Visual Basic project, i get this error. i tried to be careful to make sure i checked the "execute on main package thread" checkbox but it didnt make a difference...

has anyone seen this?I haven't seen it, but it looks like a permissioning problem. When you run the DTS package from VB are you running it with the same user details are when you run it from within Enterprise Manager?|||http://search.microsoft.com/search/results.aspx?na=84&st=a&View=msdn&qu=Provider+generated+code+execution+exception%3A+ EXCEPTION_ACCESS_VIOLATION&qp=&qa=&qn=&c=&s=4

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

DT_NTEXT pass through columns in fuzzy lookup transformation

The documentation on the fuzzy lookup transform mentions that only columns of type DT_WSTR and DT_STR can be used in fuzzy matching. I interpreted this as meaning that you could not create a mapping between an input column of type DT_NTEXT and a column from the reference table. I assumed that you could still have a DT_NTEXT column as part of the input and mark this as a pass through column so that it's value could be inserted in the destination, together with the result of the lookup operation. Apparently this is not the case. Validation fails with the following message: 'The data type of column 'fieldname' is not supported.' First, I'd like to confirm that this is really the case and that I have not misinterpreted this limitation.

Finally, given the following situation

- A data source with input columns

Field_A DT_STR
Field_B DT_NTEXT

- A fuzzy lookup is used to match Field_A to a row in the reference table and obtain Field_C.

- Finally, Field_B and Field_C must be inserted into the destination.

Can anyone suggest how this could be achieved?

Fernando Tubio

One possible workaround is using a multicast transform to route the input columns around the lookup transform. A merge join transform can then be used to join the outputs from the multicast and the fuzzy lookup to include the DT_NTEXT field back into the data flow.

I've tried this solution and it works but I wonder if it is really necessary to resort to all these contortions.

|||

It looks like your workaround is the best approach. The Fuzzy lookup does not support DT_NTEXT, DT_TEXT, or DT_IMAGE columns as copy columns OR pass-through columns. I am not sure why that is, but I will try to find out.

Mark

|||

I guess one of the reasons was performance, and that these columns require special handling. You might want to put in a request for this feature for a future release.

Thanks
Mark

|||

Thank you Mark.

Considering my limited knowledge about the inner workings of the data flow pipeline I am very likely wrong, but I would have guessed that a pass-through operation merely involved copying some pointers around. In any case, the package creator can control which columns to pass-through and if he is concerned with performance, then he is in a better position to decide whether to include these columns in the output. So I guess it would be nice to have this choice in a future release.

Fernando Tubio