Set "exported" column to 1 when exporting via DTS

Question

Set "exported" column to 1 when exporting via DTS

Andy Cutler

SSC Journeyman

Points: 90
More actions
June 5, 2006 at 4:14 am

#68529

Hi
Just a quick question regarding exporting SQL table data to a text file...
I have a column in my table called "Exported" which holds a 1 or a 0 depending on whether that row was exported to the text file correctly. Basically I'm not really using it properly because I have no process which checks whether each row has been output correctly. All I do is run a SQL Task after the whole export is successful and set all rows to 1.
Can anyone point me in the right direction to check each row and update to 1 if it's been exported? If it helps, it's for an ordering system which stores it's data in a SQL table, at the end of the day I export all these orders to a text file which is then sent to a seperate program for processing.
I'm sure people have had similiar issues to any help would be appreciated
Thanks
Andy

Viewing 11 posts - 1 through 10 (of 10 total)

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

asura SSC Rookie Points: 34 More actions · Answer 1

Hello Andy,

You may want to use a Data Driven Query Task for this. which may look up the file for correctness and then update the field.

May I suggest you to use a Checkpoint mechanism to verify this???

Thanks

Asura

Andy Cutler SSC Journeyman Points: 90 More actions · Answer 2

ok I will look into the Data Driven option.

When you say "Checkpoint mechanism", what do you mean?

many thanks

andy

asura SSC Rookie Points: 34 More actions · Answer 3

Andy,

This is a mechanism where you export each record, set the exported flag and then proceed to export the next record. This might sound a bit complex, but can be achieved with a simple VB or C# code.

Ranjit

Josep SSCommitted Points: 1518 More actions · Answer 4

I also have a 'exported' column and I use it because once exported I don't export it any more. I've selects like WHERE exported=0 to select the records to export.

I use Transform Data Task or Data Driven Query Task depending on if I have to just export the information (insert) or if I have to insert or update it, depending what I have in the target.

In both cases I use Lookups and I call them from the VBScript used by the transformation. That's the only way I've found, althought I don't like it. It takes a long time to create de DTS and is not really fast, but in the Tranformation you cannot modify the source values 🙁

Josep.

asura SSC Rookie Points: 34 More actions · Answer 5

asura

SSC Rookie

Points: 34

June 6, 2006 at 9:13 am

#642038

So do you have the issue resolved???

Andy Cutler SSC Journeyman Points: 90 More actions · Answer 6

I'll have to take a look further but i'm guessing we're on the right track

Josep SSCommitted Points: 1518 More actions · Answer 7

Yes, I've resolved it in the way I've told before. But as I said, it has the disadvantage of slow execution and long time to create the DTS.

I would believe there are a better way to do it, but I haven't found it, so That's what I have.

Also I've forgotten to say that when I set the exported column, I'm doing it before the record is really exported. So if I've an error exporting it I can have already said it's exported. The first 'if' is just to be sure that the record is not in the target database.

Is for these thinks that I don't like the solution I've found.

When I've few records and the transformation is quite complicated, I like to export into temp tables with the same fields as source table and then execute a stored procedure to transform it. It's much easy to execute SQL code in a stored procedure than in a VBScript.

Josep.

Here is an example how I do it:

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

Function Main()

' We look if the record is already imported in target

If DTSLookups("Record imported").Execute(DTSSource("FieldKey1"), DTSSource("FieldKeyN")) =0 Then

DTSDestination("TargetField1") = DTSSource("SourceField1")

........

DTSDestination("TargetFieldN") = DTSSource("TargetFieldN")

'We change the source exported column

Call DTSLookups("Set exported").Execute(DTSSource("FieldKey1"), DTSSource("FieldKeyN"))

Main = DTSTransformStat_OK

Else

' Here I log it using another Lookup because it should not enter here, but as you can see I set the exported before I really

' export the record !!!

Main = DTSTransformStat_SkipRow

End Function

Brad Hale SSC Enthusiast Points: 198 More actions · Answer 8

What I've done in the past was to Create an update query that takes the same select criteria as the export query. I set the export query in one ExecuteSQl task and then call a second ExecuteSQL tasks for the update query upon Successful execution using the same parameters (pulled from Global Vars).

HTH

Andy Cutler SSC Journeyman Points: 90 More actions · Answer 9

That is how I've setup my DTS jobs up until now but I would like to look into this further, thanks tho

Josep SSCommitted Points: 1518 More actions · Answer 10

Sorry Brad, but I don't understand how do you do it. Could you explain it a little more?

Thanks