locked
Don't write Flat File Destination if Conditional Split is not true RRS feed

  • Question

  • Hi friends,

    i have a Problem with my destinations.

    I have a split condition with two ways the flow can use.
    In this case: all and Date.

    All and Date can be set by using a variable.
    Its working good.

    When a user fills the variable with a date value (cast to string) the conditional split executes the correct flow with all the needed rows... The same time the all flow will be executed with 0 rows.
    In the end the destionation file for the all values will be overwritten with nothing.

    The same on the other hand when a user fills the variable with the all value, the date file is empty.

    What can i do to make sure that the files are not empty?

    Best regards,
    Sebastian

    Friday, May 8, 2015 2:58 PM

Answers

  • Hi Sebastian,

    The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. Please note that the Conditional Split directs a row to a single output. So if one row loads to one output, it wouldn’t appear in other outputs. This is the reason why one file has all data, another file is empty.

    The Multicast transformation is similar to the Conditional Split transformation. The difference between the two is that the Multicast transformation directs every row to every output, and the Conditional Split directs a row to a single output. Multicast transformation is useful whenever you wish to make many copies of same data or you need to move the same data to different pipelines.

    So in your scenario, please use Multicast transformation to achieve your requirement. For more details, please see:
    http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, May 11, 2015 7:06 AM

All replies

  • Just orchestrate the execution flow using the Precedence Constraints https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/

    Arthur

    MyBlog


    Twitter

    Friday, May 8, 2015 3:41 PM
  • Hi Sebastian,

    The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. Please note that the Conditional Split directs a row to a single output. So if one row loads to one output, it wouldn’t appear in other outputs. This is the reason why one file has all data, another file is empty.

    The Multicast transformation is similar to the Conditional Split transformation. The difference between the two is that the Multicast transformation directs every row to every output, and the Conditional Split directs a row to a single output. Multicast transformation is useful whenever you wish to make many copies of same data or you need to move the same data to different pipelines.

    So in your scenario, please use Multicast transformation to achieve your requirement. For more details, please see:
    http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, May 11, 2015 7:06 AM