Appending to a text file via DTS

DTS wizard is not allowing me to append the data to a text file. Every time I run DTS and choose the destination to be this text file (say A.txt), it overwrites the data. I have a table whose data I am dumping to a text file. I truncate the table, then get the data again into it and want to append it to the same text file. But I end up overwriting the text file with the new data.

Kindly let me know where I am going wrong.I am having the same issue as well... I thought I remember seeing an option checkbox somewhere to do that... but I cant seem to find it.
I doubt we are the only ones having to deal with this, so can someone please chime in with an answer or maybe a possible direction for us to follow? Thanks in advance... :)

Appending to a recordset destination

Hi,
We have a data-flow that extracts from a raw file and inserts into a datareader destination.

The data-flow requires DelayValidation=TRUE because of the raw file source (http://blogs.conchango.com/jamiethomson/archive/2005/12/01/2443.aspx)

Unfortunately it also requires DelayValidation=FALSE because of the Datareader destination (http://blogs.conchango.com/jamiethomson/archive/2005/10/20/2284.aspx)

So basically we can't do it this way.

We'd like to replace the raw file destination with a recordset destination but we have to be able to append to it.

Is there a way of appending when using a recordset destination?

Thanks in advance!
-JamieJamie,

I believe that your first statement is not correct for the raw file source (just for the raw file destination) as long as you have already created the raw file before the dataflow with the raw file source validates. So you could create a empty raw file with just the correct metadata and then set delay validation to false and what you want to do should work.

In answer to your question, I do not believe we have a way to append using a datareader dest.

Thanks,
Matt|||Matt,
That's true - but I don't really want to be moving raw files into a production envioronment. We want to deploy the packages and nothing else!

We are thinking of writing a custom destination adapter that appends to a recordset. Do you think that is possible?

I guess its a question of which is the least nasty workaround so your suggestion IS something to consider.

-Jamie

P.S. Is it worth raising a DCR asking for the ability to append to a recordset destination? This would mean it could be used in place of a raw file which could be rather useful.|||Hi Jamie,

I am certain it is possible to create a custom destination adapter to do what you want. I don't know that it wouldn't be really nasty to do so. I think the empty raw file is a little more hacky but much easier to do.

I can't see why you couldn't raise this as a DCR. I don't know whether it would make it into the next version or not but that isn't reason not to ask.

Thanks,
Matt|||OK. Well I have done. :)

http://lab.msdn.microsoft.com/ProductFeedback/thankyou.aspx?FeedbackID=FDBK42005&submit=true

-Jamie

Append Unicode string source to non-Unicode string destination

In SQL 2000 DTS, I was able to append data from an ODBC source to a SQL 2000 destination table. The destination table was created by copying an attached source table in Access to a new table, then upsizing it to SQL. The character fields come over as varchar, and that seemed to be fine with the DTS job.

Now using the same source table and the same SQL destination, only in SQL 2005 with Integration Services instead of DTS, I get an error because the connection manager interprets the source text fields as Unicode and the destination fields are varchar.

I could script the table and change the text fields in the destination table to nvarchar, but this could have adverse affect on the application that uses the destination table. Is there a way to make the connection manager see the source text fields as varchar, or have the integration package allow the append even though the destination is varchar and the source is nvarchar?

Use the Data Conversion component to change the values from DT_WSTR to DT_STR.

-Jamie

|||Thanks for the quick response. I was able to finish the SSIS package successfully.

Append Query Problem

I'm testing this query and it does not append any new rows from the
source tables unless the destination table is empty. I want to design
the query just to add new records from the destination tables and leave
existing ones in tact. Help appreciated. Query:

INSERT INTO tMASmembers ( Division, CustomerNumber, CustomerName,
AddressLine1, SalesPersonCode, SIC_Code, SIC_Desc )
SELECT AR1_CustomerMaster.Division, AR1_CustomerMaster.CustomerNumber,
AR1_CustomerMaster.CustomerName, AR1_CustomerMaster.AddressLine1,
AR1_CustomerMaster.SalesPersonCode, AR_90_UDF_AR_Customer.Sic_Code,
AR_90_UDF_AR_Customer.Sic_Desc
FROM AR1_CustomerMaster INNER JOIN AR_90_UDF_AR_Customer ON
(AR1_CustomerMaster.Division = AR_90_UDF_AR_Customer.Division) AND
(AR1_CustomerMaster.CustomerNumber =
AR_90_UDF_AR_Customer.CustomerNumber)
WHERE (((AR1_CustomerMaster.Division) Not In (Select
[tMASmembers].[Division] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerNumber) Not In (Select
[tMASmembers].[CustomerNumber] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerName) Not In (Select
[tMASmembers].[CustomerName] From [tMASmembers])) AND
((AR1_CustomerMaster.AddressLine1) Not In (Select
[tMASmembers].[AddressLine1] From [tMASmembers])) AND
((AR1_CustomerMaster.SalesPersonCode) Not In (Select
[tMASmembers].[SalesPersonCode] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Code) Not In (Select
[tMASmembers].[SIC_Code] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Desc) Not In (Select
[tMASmembers].[SIC_Desc] From [tMASmembers])));

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!One more note: In a test I did, it seems I can only append when I delete
the records from the destination table that all belong to an entire
division (01 or 02). So if I delete all the customers from div 01, I can
append them back. The division and customer number fields are the
primary keys and they come in as text from the ODBC import.

Help appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Frank Py" <fpy@.proactnet.com> wrote in message
news:3ffd9cd7$0$70302$75868355@.news.frii.net...
> One more note: In a test I did, it seems I can only append when I delete
> the records from the destination table that all belong to an entire
> division (01 or 02). So if I delete all the customers from div 01, I can
> append them back. The division and customer number fields are the
> primary keys and they come in as text from the ODBC import.
> Help appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You probably want something like this:

insert into tMASmembers (...)
select (...)
from
AR1_CustomerMaster CM inner join AR_90_UDF_AR_Customer C
on CM.Division = C.Division and CM.CustomerNumber = C.CustomerNumber
where not exists
(select * from tMASmembers tm
where tm.Division = CM.Division and
tm.CustomerNumber = CM.CustomerNumber)

Simon|||Thanks for the example.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Append only unique records in SQL table

I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.

The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?

Thanks in advance.
SMc

Lets say your destination table is called Dest and you temporary table is called Temp.

Both tables have two fields, A and B where A is the one that needs to be unique in Dest, but it's not necesarily unique in Temp (or Temp contains rows with a value of A that already exists in Dest).

insertinto Dest

select Temp.A, Temp.Bfrom Temp

where Temp.Anotin(select Dest.Afrom Dest)

|||

I was just fighting with similar, but a lot of simpler problem. Maybe these posts will help.

http://forums.asp.net/t/1185782.aspx

And IF NOT EXISTS(SELECT * FROM table WHERE .....

Hope it helps

Leif

Append new records from ODBC source

I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?

Steve Jensen wrote:

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

Steve,

Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior

|||

Sounds good. Could you give me some detailed instructions for doing this?

Thanks,

Steve

|||

Steve here is a detailed description:

http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

I Hope this can help you

Rafael Salas

|||

Sorry, but I'm still at the 'first, you drag a data flow task onto the design surface ...' stage. I understand the concept but need a more step-by-step on how to do it.

Thanks,

Steve

Append new records from ODBC source

I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?

Steve Jensen wrote:

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

Steve,

Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior

|||

Sounds good. Could you give me some detailed instructions for doing this?

Thanks,

Steve

|||

Steve here is a detailed description:

http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

I Hope this can help you

Rafael Salas

|||

Sorry, but I'm still at the 'first, you drag a data flow task onto the design surface ...' stage. I understand the concept but need a more step-by-step on how to do it.

Thanks,

Steve