How to select, write to flat file destination and update table records in ssis

  • I am new in SSIS, i would appreciate if anyone can provide me solution to my problem

    My requirement as mentioned below...

    1. Read records from a source table using SQL Query (e.g Select * from MyTable where Status='S')

    2. If record(s) found, write to a text file

    3. If record(s) found, Execute an external executable with some arguments to process text file data (i have an application which will process the records)

    4. Once above is done, records's status should be updated e.g Status = 'D'

    Thanks in advance

  • shahzadazizpk (10/4/2015)


    I am new in SSIS, i would appreciate if anyone can provide me solution to my problem

    My requirement as mentioned below...

    1. Read records from a source table using SQL Query (e.g Select * from MyTable where Status='S')

    2. If record(s) found, write to a text file

    3. If record(s) found, Execute an external executable with some arguments to process text file data (i have an application which will process the records)

    4. Once above is done, records's status should be updated e.g Status = 'D'

    Thanks in advance

    1. Data Flow Task with an OLEDB or ADO.Net Source

    2. RowCount Transform after the Source. Conditional Split after the RowCount with RowCount > 0 as the check then with that OUtput sent to a flat file destination

    3. In the Control Flow after the data flow task an Execute Process Task with a Precedence Constraint set to SUCCESS and Expression with the Expression set to RowCount > 0

    4. Execute SQL Task after the Execute Process Task with a precedence constraint of succeess that Updates teh Source Set Status = 'D' where Status = 'S'.

  • Thank you for your reply!

    I have tried the mentioned steps but stuck when i run the application, actually Row Count is not updating the value of the variable until data flow task is not completed, because of that flat file is not created

    I searched on internet and found that this is a know issue and variable is updated upon completion of a data flow task

    do you have any idea how can i deal with this situation ?

  • shahzadazizpk (10/5/2015)


    Thank you for your reply!

    I have tried the mentioned steps but stuck when i run the application, actually Row Count is not updating the value of the variable until data flow task is not completed, because of that flat file is not created

    I searched on internet and found that this is a know issue and variable is updated upon completion of a data flow task

    do you have any idea how can i deal with this situation ?

    I've never seen or heard of that issue with the variable not being updated until the data flow task is completed. Can you post a link? Also what version of SSIS?

  • Microsoft SQL Server Integration Services Designer

    Version 10.50.1600.1

    SQL Server 2008 R2

    Development environment Microsoft Visual Studio 2008

  • shahzadazizpk (10/5/2015)


    Microsoft SQL Server Integration Services Designer

    Version 10.50.1600.1

    SQL Server 2008 R2

    Development environment Microsoft Visual Studio 2008

    Thanks. I also tested the RowCount thing in SSIS 212 and see the same behavior.

    A workaround would be:

    1. Execute SQL Task that populates the rowcount variable

    2. Sequence Container with the rest of the work in it, with the precedence constraint from the EST to the Sequence Container set to Success and Expression (RowCount > 0).

    If you have rows you are sending the query twice which isn't ideal, but I think it'll work.

  • Now it is working but solution is not optimized, as you see i have to do following task which is not so complex

    Read Records -> Has Rows -> Write to flat file -> Process file with external executable -> Update records status

    here i would need your help to optimizing the execution because DBA will validate the package and will argument if not optimized

    During R&D I have seen people are using Cache transformation for optimization purpose, if you have any idea how can we use this in our case it would be great

    This should be done with single query, if i fetch record again and again it will be a burden to SQL Server and also result may not be accurate

    let suppose we have four records to process with Status ='S' and these are written in the file, meanwhile backend system added added another entry with Status='S' what will happen

    Update statement will update all the records without taking care that record is processed by executable or not

Viewing 7 posts - 1 through 6 (of 6 total)

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