locked
SSIS Conditional Split RRS feed

  • Question

  •  i created a small DTS flow which is fetching data from OLD DB source to two  flat files using condition given in Conditional Split Transformation

     

    Conditional Spilt has two condition according to source table column as given

    Case1      Department == "IT"

    Case2      Department == "Management"

    Case3      Department == "Sales"

    now as per this Case "IT", "Management" and "Sales" i am generating the flatfiles in three location C:\IT , C:\Management , C:\ Sales folder respectevly

     

    this package is running fine but here the logical problem occurs, All the files are generating with accurate records in there respective folders but simultanuasly two other file also generating in all folders. which is not required,

    it seems the condition which get satisfied executes and generate the file in there respective folders and fill the record in that file but other 2 case2 is also executing simultaneously and generating blank files

    Assistance is highly appreciable.

    Thanks in advance

     

     

     

     

     

    Thursday, November 25, 2010 9:24 AM

Answers

  • It sounds like you have some additional cases on the split, which also create files. Whilst no data is flowing down that case path, a flat file is always created, even if no rows are written. This is standard behaviour.

    You could use a Row Count Tx to indicate empty files, and have secondary tasks that execute when the row count is zero and delete the empty file. You would use a precedence constraint to control the delete file task with an expression, e.g. @MyRowCountVar == 0.


    http://www.sqlis.com | http://www.konesans.com
    • Proposed as answer by Jamie Thomson Thursday, November 25, 2010 10:17 AM
    • Marked as answer by KJian_ Thursday, December 2, 2010 8:35 AM
    Thursday, November 25, 2010 10:16 AM

All replies

  • Hmmm...that's not good. Tell me more. How are you defining the location of those files? Do you have three separate connection managers? Do you set the location dynamically using an expression? If so, what is that expression? etc...
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Thursday, November 25, 2010 9:46 AM
  • It sounds like you have some additional cases on the split, which also create files. Whilst no data is flowing down that case path, a flat file is always created, even if no rows are written. This is standard behaviour.

    You could use a Row Count Tx to indicate empty files, and have secondary tasks that execute when the row count is zero and delete the empty file. You would use a precedence constraint to control the delete file task with an expression, e.g. @MyRowCountVar == 0.


    http://www.sqlis.com | http://www.konesans.com
    • Proposed as answer by Jamie Thomson Thursday, November 25, 2010 10:17 AM
    • Marked as answer by KJian_ Thursday, December 2, 2010 8:35 AM
    Thursday, November 25, 2010 10:16 AM