Post reply

Set "exported" column to 1 when exporting via DTS

  • 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

  • June 5, 2006 at 6:22 am

    #641633

    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

  • June 5, 2006 at 8:00 am

    #641701

    ok I will look into the Data Driven option.

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

    many thanks

    andy

  • June 5, 2006 at 10:36 am

    #641786

    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

  • June 6, 2006 at 9:00 am

    #642028

    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.

  • June 6, 2006 at 9:13 am

    #642038

    So do you have the issue resolved???

  • June 6, 2006 at 9:14 am

    #642039

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

  • June 6, 2006 at 9:55 am

    #642061

    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

  • June 7, 2006 at 10:00 am

    #642326

    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

  • June 8, 2006 at 10:43 am

    #642595

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

  • June 9, 2006 at 1:13 am

    #642712

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

    Thanks

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

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