export table as text file with a job ?

Question

export table as text file with a job ?

markuss21

Old Hand

Points: 340
More actions
January 10, 2006 at 9:05 am

#91168

hi all!
im a newbie in TSQL but I have to prgramm an table export.
The table have a 'flag' column. And now I will only the rows to export which have a 0 in the column flag!
And now I have to export this selection in a text file.
After the successful export the culumn 'flag' have to change into the number 1.

How can I do that ? I have no solution and I'm a newbie in TSQL 🙁
I hope that anybody can help me with an example !?
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 1

You need to create a Data Transformation Services Package. In Enterprise Manager open up the database you are exporting from and click on the toolbar icon with a wand and some stars. Expand Data Transformation Services, highlight Data Export Wizard and click OK. Follow the wizard through to choose your destination file name and format. At the "Specify Table Copy or Query" stage choose "Use a query" and enter something like "SELECT * FROM Table1 WHERE Flag=0". At the end of the process deselect the "Run immediately" box and select "Save DTS Package". Click OK. Enter a memorable name and click OK. Back in Enterprise Manager expand Data Transformation Services, Local packages and doubleclick on the package you saved earlier. On the Task toolbar click on the icon that looks liek an oil barrel with some revolving red arrows on it. In the SQL statement put something like "UPDATE Table1 SET Flag=1 WHERE Flag=0". Click on the Text file icon in the main window, Control-click on the icon you have just created and then right-click on it. From the popup menu choose Workflow, On Success. Save the opackage. You can now run it by clicking the green triangular icon on the toolbar.

--
Scott

markuss21 Old Hand Points: 340 More actions · Answer 2

At first, thanks for this quickly answer !

Next problem. I have to do this packet every day at 20:00 during the week. Can I make a Job for this DTS Package ?

Thanks !

Markus

markuss21 Old Hand Points: 340 More actions · Answer 3

I have fount out it by myself.

But I have a further question:

It is possible to catch errors? I want to have a textfile or something else when the export was faulty.

Thanks

Markus

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 4

You can set the job to send an alert when it fails. In Enterprise Manager expand Management then Operators. Right-click in the right hand window and select New Operator, then enter your details. Go to the Jobs section, double click on the scheduled job you have created and click on the Notifications tab. Here you can set the details of what is to happen when the job fails.

Will this do what you need?

--
Scott