Solved

How do I get my Export DTS Job to append data, not overwrite it?

Posted on 2004-09-03
20
528 Views
Last Modified: 2013-11-30
I've got a DTS Job that locks a table (hopefully) and exports all the data with the status 'X'. It then updates the status to 'Y'. I need to have the job run once an hour, in order to export the new data that came in. When the job runs it overwrite the data that I've already exported. Where is the parameter I need to set, to tell DTS to append the data?
0
Comment
Question by:DSchat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
20 Comments
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11972301
a) Export how?
b) if you are using a select into to place it in a new table
    change the code to insert into. this will append the data to that table

if you are creating an extract file then do this

create a table call it Daily Updates
then insert your selections into it each time
then create your extract file from the "daily update" table

Jay
0
 

Author Comment

by:DSchat
ID: 11972334
I'm exporting right now into a text file. The option of first exporting in a table and then just once a day exporting that data to my file text was an option that I was also considering. I was wanting to see if I could change a setting inside SQL to get it to append.
0
 
LVL 10

Accepted Solution

by:
Jay Toops earned 65 total points
ID: 11972562
there is no setting in the data pump to append
however you could write an active X task to create the file
and use a filesystem object and append to that.
of course that means you would have to write the code to output
your file manually.


Personally id take the create table option Above.

Jay
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 34

Expert Comment

by:arbert
ID: 11973740
"there is no setting in the data pump to append "

The datapump WOULD append unless your job also has a truncate or delete statement in it somewhere....

What if you get the same data again for some reason?  How do you want to handle that?
0
 

Author Comment

by:DSchat
ID: 11988938
I can't get the same data again, because after I exported I update my 'Status' column from X to Y. I only want the data with the value Y.

What I did was use the DTS Import/Export Wizard, pick out from Server.DB.table I wanted to export (with a select), I chose my type and place of my destination (a text file on another server). I saw a button that I could mark to append data, but it was greyed out. My only choice there was to create a table. Why does thta come when I just export to a text file?
0
 

Author Comment

by:DSchat
ID: 11989542
I think I might have found a way to append, but I don't know how I implement it. I need to set the AppendIfFileExists Property. I think it works with:

Set transprops = transform.TransformServerProperties
transprops"AppendIfFileExists") [= boolean]

Where and how do I put this SET Command in?
0
 
LVL 34

Expert Comment

by:arbert
ID: 11991234
" My only choice there was to create a table. Why does thta come when I just export to a text file?"

Because you're using the wizard...If you edit the package after you create it with the wizard, you can get rid of any CREATE/TRUNCATE table statements and by default it WILL append the data...
0
 

Author Comment

by:DSchat
ID: 11996684
Do you mean in "design package"? I looked in there and I found nothing with CREATE/TRUNCATE. Is there any other place where you can edit a package? Is the package stored in a database or as a flat flie somewhere?
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 60 total points
ID: 12009444
Yes, that's the only place....The data in the table will NOT be truncated automatically.  Unless you have an explicit delete or truncate in your DTS job, the data will automatically append to the table (as long as you don't have primary key constraint violations of course).

What else does the package do?
0
 

Author Comment

by:DSchat
ID: 12042967
I've looked through all the different places (properties) where you can set the different parameter and there is nothing where I could set append. Also I could not find any place where a TRUNCATE is specifically refered to. I believe that the truncate is implicitly started.

I've tried it without using the wizard also. I've created an OLE DB connection and the object that I am exporting to is a text file. The export is done with a transform data task which has a simple SQL in the background, which defines what is to be exported.

It made no difference, the data was overwritten each time.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12059724
"I believe that the truncate is implicitly started."

Not in DTS...Text Files and Tables are different stories....

IF YOU HAVE A DTS JOB WITHOUT ANY DELETE OR TRUNCATE STATEMENTS, IT WILL APPEND TO THE TABLE.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12059963
There comes a point when u stop beating your head against a wall and just do it
this way:

create a table call it Daily Updates
then insert your selections into it each time
then create your extract file from the "daily update" table

Jay
0
 

Author Comment

by:DSchat
ID: 12062014
Thanks Jay, but your suggestion brings us back to the point we were at, when I started this question. My problem is that the table is exported once a day and if for some reason the exported data isn't tranported to the host on one day because of an error on that side or if is weekend (when the host isn't there) the export is triggered at it's scheduled time and it overwrites the text file that hasn't been picked up yet and my data is gone!

To arbert's comment: "Text Files and Tables are different stories...." how so? I just tested this myself, exporting into a table and saw that here the data is appended. So this is my difference between tables and text files. I used the same SQL to select which data to export. Everything is the same except the goal. This time a table instead of a text file.

But now my question is coming back around to the beginning, is there a way to append onto an exported text file? I need to do this for the reasons stated above.

Thanks for the help,
Doug
0
 

Author Comment

by:DSchat
ID: 12274443
I think I now have the solution myself. I will do the export to a text file and upon success I will start a *.bat/cmd file that copies the file to another location and when it already exists it will be appended.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12276613
I disagree
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12277824
split Jltoops and Arbert

Jay
0
 

Author Comment

by:DSchat
ID: 12284928
Jay, you and Arbert gave me some good tips but it came out that DTS just overwrites a text file when you export to it again. I then figured out that I could use an Execute Process Task to copy the file to another location without overwriting. I believe this is more or less what you were suggesting in your comment with an activeX script. In the end I just did it with a CMD file with the command: "type filename1 >> filename2" It is still all controlled with DTS which is what I wanted.
So because of what both of you said, I will split the points, the tips from you guys will be useful to me. Do we have things settled now?

Doug
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question