DTS Export Wizard

Is there a way for me to globally select "Delete rows in destination table"
when performing an export via the Export Wizard?
It is not obviously visible to me, and I have a great deal of tables. I've
been clicking "Edit" for each one, and this isnt practical for obvious
reasons.
Can anyone point me in the right direction? Thanks.there is an option under 'select object...' dialog.
http://msdn.microsoft.com/library/e...elpwiz_0nci.asp
or you can create a dml task and delete the data before insert new ones.
-oj
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:eAbKC$1TGHA.4608@.tk2msftngp13.phx.gbl...
> Is there a way for me to globally select "Delete rows in destination
> table" when performing an export via the Export Wizard?
> It is not obviously visible to me, and I have a great deal of tables. I've
> been clicking "Edit" for each one, and this isnt practical for obvious
> reasons.
> Can anyone point me in the right direction? Thanks.
>

DTS Error when using text file as destination in DataPump Task

I want to export to a Text File (destination) from a SQL query using a DTS DataPump Task. My query has 28 columns, some how when I try to define the destination columns for my text file the Microsoft Managment Console Crashes completely.

I tough may be my query has two many columns only to find out that it has one to many. If I ramove a column form my query, any column. I get no error at all.

Is there a limit to a Text File destination connection?I got the same problem. I cannot imagine it has anything to do with nr of columns.
Would be interested in solution...

dajm|||I do believe there is a limit to the number of columns but it can't be 28. Still since there's no error besides the crash I have not figured it out yet. Im going to try some test querys.|||I know this isn't what you asked, but I always use bcp for this. Are you doing complex transformations in the data pump? If it is just SQL, you can go to a command line (execute process task if you want DTS to do it) and run bcp "your query" queryout destination.txt -c -T -t "|" (for pipe-delimited). If it is an entire table (or view), you can run bcp "tablename" out destination.txt -c -T -t"|"

DTS error on GUID column

I am doing a DTS from table "source" to table "destination". However
"destination" has a primary key of data type uniqueidentifier and the source
doesn't have a corresponding map field. So I thought setting the
"destination" primary key to have a default value of newId() will
automatically insert a guid for every record inserted (just like a regular
identity key) but unfortunately this doesn't work. I get a message that says
"cannot insert null into the field fieldName" which makes sense because it i
s
the PK but why doesn't the newId() generate an automatic GUID? Or is it even
possible? or could I change the transformation script to make it work and if
so, how? Thanks for any help.On guid primary key column undid primary key constraint, set allow null, set
default value to newId() and DTS worked fine and new guids got inserted.
After DTS set all constraints back as original. So everything's good.
"Naveen" wrote:

> I am doing a DTS from table "source" to table "destination". However
> "destination" has a primary key of data type uniqueidentifier and the sour
ce
> doesn't have a corresponding map field. So I thought setting the
> "destination" primary key to have a default value of newId() will
> automatically insert a guid for every record inserted (just like a regular
> identity key) but unfortunately this doesn't work. I get a message that sa
ys
> "cannot insert null into the field fieldName" which makes sense because it
is
> the PK but why doesn't the newId() generate an automatic GUID? Or is it ev
en
> possible? or could I change the transformation script to make it work and
if
> so, how? Thanks for any help.

DTS Error

Hi,
Can any body help me to sort out this problem

I am running a DTS package source as dbf file and destination as sqlserver database the following error occurs

for row number 2035 errors encountered so far on this
task 1. insert error, column 3 ('arch_dt', DB_TYPE DBTIMESTAMP)

when i was opening dbf file in foxbase its showing (arch_dt field) date as datatype the same when i was importing to access the datatype is changing to text type.

so daily manually i am opening in excel file and delete the records where the line error occurs.

in excel file that particular row will have some text format instead of dateformat.

how can i solve this error without manual interaction..

with regards
rajkumarIf you insert it as a chracter datatype then you can check the value and correct it.
You could also probably use an activex script on it but that would be slow.

DTS- Destination Column Name has spaces causing error

I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?

I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.

When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Use square brackets ( [ ] ) , instead of double quotes.|||

I tried this in a sql query against the Advantage ODBC and it failed. I believe this is a limitation of the ODBC drivers for Advantage.

The issus that the column names are causing this to fail inside the DTS Transformations themselves. Anyone know where I can edit the Destination column names so that I can wrap them with quotes or brackets.

Thanks

Donnie

|||

Have you tried enclosing the column names in single quotes instead of Microsoft brackets?,

i.e. SELECT 'My Column' FROM MyTable

|||

Yes tried single quotes.

If I try Insert into TableName ("Last Name") values ('Test') with the Advantage ODBC, it works. Issue is, I need to pull from SQL and push into Advantage. When I try with with transformations between the source and desitination, I get the missing closing ")" error.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Thanks for the help

Donnie

|||

This forum is for SSIS, not DTS.

You'll probably get better assistance on the DTS newsgroup.

|||Thanks DD. Got it over there as well as Google forums.|||Bumpsql

dts copy text to table only

how do i specify the table destination only not the whole database
from a text.txt source?
thanks,
trintYour question has been answered in
microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"trint smith" <trintsmith@.hotmail.com> wrote in message
news:e7081ac9.0307260802.4c8acc5a@.posting.google.c om...
> how do i specify the table destination only not the whole database
> from a text.txt source?
> thanks,
> trint|||Your question has been answered in
microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"trint smith" <trintsmith@.hotmail.com> wrote in message
news:e7081ac9.0307260802.4c8acc5a@.posting.google.c om...
> how do i specify the table destination only not the whole database
> from a text.txt source?
> thanks,
> trint

DTS copy table from source DB->destination DB

I have started to create a package in DTS. I have created 2 connections for source DB and destination DB. I want to schedule a task to copy data from source to destination

I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.

I just want to transfer the data and schedule it but when I ran the package it said:

'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'

I re-opened the 'Data Driven Query Task'

I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:

INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()

But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.

Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?

Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.

So, when I run it now I get the error

'The data driven query task requires at least one query (and associated columns) to be specified'

I don't want to set any queries. I just want the transformation to run when i execute the package.

Any ideas?|||Why not just schedule an SP to run to copy the data?

For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation

Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?

For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation

Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>

Thanks,

I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.

i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.

DTS Check Source

I'm trying to export data from oracle db (source) to a SQL Server db (destination). How can I check that there is data in the source db first before exporting the data.
Cheers
Cat,
you can count the records in one of the tables you are referring to and then
put this value into a global variable as an intial step. After that you
execute the import dependant on the global variable value. Using multiple
paths in a workflow is a way to implement this :
http://www.sqldts.com/default.aspx?218.
HTH,
Paul Ibison

DTS Check Source

I'm trying to export data from oracle db (source) to a SQL Server db (destination). How can I check that there is data in the source db first before exporting the data
CheersCat,
you can count the records in one of the tables you are referring to and then
put this value into a global variable as an intial step. After that you
execute the import dependant on the global variable value. Using multiple
paths in a workflow is a way to implement this :
http://www.sqldts.com/default.aspx?218.
HTH,
Paul Ibison

DTS Check Source

I'm trying to export data from oracle db (source) to a SQL Server db (destin
ation). How can I check that there is data in the source db first before exp
orting the data.
CheersCat,
you can count the records in one of the tables you are referring to and then
put this value into a global variable as an intial step. After that you
execute the import dependant on the global variable value. Using multiple
paths in a workflow is a way to implement this :
http://www.sqldts.com/default.aspx?218.
HTH,
Paul Ibison

DTS Buffer times out when inserting to sql destination

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

What happens when you replace the SQL Server destination with an OLE DB destination?|||

Amna wrote:

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

How big is the data set being processed?

It is likekly the SQL Server destination is trying to insert the whole data set in one transaction. If you use OLE DB DEtination you will have more control over the commit and batch size.

|||The problem is when there are NO records. When there are records to be inserted, SQL server destination works fine. But when there are no records, it kinds of start waiting for the records and then times out. Any ideas?|||

Hi Amna,

feel free to open a bug for this on the connect site.

In the meantime, you should use the OLE DB destination with Fast Load. It should not have a significant impact on the performance.

Thanks,

-Bob

DTS Buffer times out when inserting to sql destination

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

What happens when you replace the SQL Server destination with an OLE DB destination?|||

Amna wrote:

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

How big is the data set being processed?

It is likekly the SQL Server destination is trying to insert the whole data set in one transaction. If you use OLE DB DEtination you will have more control over the commit and batch size.

|||The problem is when there are NO records. When there are records to be inserted, SQL server destination works fine. But when there are no records, it kinds of start waiting for the records and then times out. Any ideas?|||

Hi Amna,

feel free to open a bug for this on the connect site.

In the meantime, you should use the OLE DB destination with Fast Load. It should not have a significant impact on the performance.

Thanks,

-Bob

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 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 and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
Thanks
Hi
There are a couple of mentions for your error message
http://search.microsoft.com/search/r...=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the end
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
ThanksHi
There are a couple of mentions for your error message
http://search.microsoft.com/search/...a=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the en
d
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
ThanksHi
There are a couple of mentions for your error messag
http://search.microsoft.com/search/results.aspx?view=msdn&st=a&na=81&qu=&qp=Deleting+data+in+a+linked+table+is+not+supported+by+this+ISAM&qa=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the end
of the cells you have previously cleared.
John
"Stephen" wrote:
> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS ActiveX Script

Hi All,
I have the following NewBie Question:

If I have the sources:
StartDate: e.g. 23/3/2002
ApproveDate: e.g. 26/7/2002

And I want the destination be count in no.of days
(TotalDays), what should I write in the ActiveX Script?

Please give me some hints, thanks!

RickSolved.

DTS - Write Exceptions to table?

Can DTS write exceptions to a database table? In the code
below, I copy fname, lname and (at the end) company from
the source to the destination. However, with title, if the
title is executive or president, the title is copied over;
if the title is ceo it is entered into the destination
table as ceo/chairman; if none of these conditions apply
(i.e. title is owner) I want the entire row written to a
new table.

If I use Main = DTSTransformStat_SkipRow, rows that meet
the title criteria are entered into the destination table,
and the rows that don't meet the criteria are not entered.
I can get DTS to output the exeption file to a text file,
however, with the large number of records that don't meet
the criteria, it would be better to have them in a new
table.

Thanks,

Jay

----------

Function Main()
DTSDestination("fname") = DTSSource("fname")
DTSDestination("lname") = DTSSource("lname")

'Check Titles
If lcase(DTSSource("title")) = "executive" then
DTSDestination("title") = DTSSource("title")
ElseIf lcase(DTSSource("title")) = "president" then
DTSDestination("title") = DTSSource("title")
ElseIf lcase(DTSSource("title")) = "ceo" then
DTSDestination("title") = "CEO/Chairman"
Else
Main = DTSTransformStat_ExceptionRow
'Main = DTSTransformStat_SkipRow
Exit Function
End if



DTSDestination("company") = DTSSource("company")
Main = DTSTransformStat_OK
End FunctionYou can break your task in 2 phases:

First Transformation Task:
Insert / Update all rows that meet the criteria

Second Transformation Task:
Insert rows that does not meet the criteria in a anther table

Remeber the you can have more than one transformatrion task between two connections|||Thanks - I will give that a try.

DTS - Need to Set Destination Filename dynamically

I have a stored procedure that (after selecting, incrementing and updating)
returns the next sequential filename.

I haven't been able to plug it in to the destination textfile filename in my
DTS package.

I know it has something to do with Dynamic Properties and I can navigate to
the property I want to set, but just can't seem to get the result of my
stored procedure into the filename.

I know somebody must have done this before.

Thanks
JoeOne method is to assign the file name value to a global variable in an
Execute SQL task that executes your proc. You can then use a Dynamic
Properties task to assign that global variable value to the desired
property.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Joe Hurzeler" <joe.hurzeler@.verizon.netwrote in message
news:Xns988590E745995Grok@.199.45.49.11...

Quote:

Originally Posted by

>I have a stored procedure that (after selecting, incrementing and updating)
returns the next sequential filename.
>
I haven't been able to plug it in to the destination textfile filename in
my
DTS package.
>
I know it has something to do with Dynamic Properties and I can navigate
to
the property I want to set, but just can't seem to get the result of my
stored procedure into the filename.
>
I know somebody must have done this before.
>
Thanks
Joe