none
Conditional Split from a String

    Question

  • Hi All,

    I importing data from a flat file with one column up to 255 characters long. I want split the rows in if a string "BOS" is in the row.

    I have data flow task with a flat file connection to a conditional split with the following;

    ORDER 1, FINDSTRING("BOS",[Column0],1)>0

    ORDER 2, SUBSTRING([COLUMN 0],1,255)=="BOS"

    I have tried both of these conditions but neither of them work, can anyone suggest a solution.

    Thanks

    Tuesday, September 04, 2012 6:53 AM

Answers

  • problem could be with the column value.

    you are using value from [column] directly, that could have been the problem.

    try to save column values into some variables and use them into FINDSTRING function.

    Good Luck..


    Regards, Chirag Patel (ETL Engineer)

    • Marked as answer by Eileen Zhao Friday, September 14, 2012 6:21 AM
    Friday, September 07, 2012 1:47 AM

All replies

  • Try:

    ORDER 1, FINDSTRING([Column0],"BOS",1)>0

    See:

    FINDSTRING(character_expression, searchstring, occurrence)

    character_expression

    Is the character string to search in.

    searchstring

    Is the character string to search for.

    occurrence

    Is a signed or unsigned integer specifying which occurrence of searchstring to report

    http://msdn.microsoft.com/en-us/library/ms141748.aspx


    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog


    BizTalk Message Archiving - SQL and File
    Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
    Sending IDOCs using SSIS

    • Proposed as answer by SSISJoostModerator Tuesday, September 04, 2012 7:29 AM
    • Unproposed as answer by Cameronh Thursday, September 06, 2012 4:56 AM
    Tuesday, September 04, 2012 7:19 AM
  • It's not totaly clear what you're trying to accomplish with "split the rows".

    I think the search word BOS is on the wrong place in your expression:

    FINDSTRING([Column0],"BOS", 1) > 0

    Any casing problems? Try FINDSTRING(UPPER([Column0]),"BOS", 1) > 0


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by btsbee Tuesday, September 04, 2012 8:42 AM
    • Unproposed as answer by Cameronh Thursday, September 06, 2012 4:57 AM
    Tuesday, September 04, 2012 7:23 AM
    Moderator
  • I have a error log text file it doesn't contain columns is just flat data that uses [CR,LF] for the lines. I am trying to search for a string "BOS". If it exists then count the occurrences or export the data to another flat file.

    I have a flat file connection toa data conversion(convert data to DT_STR) then conditional split.

    Order 1, FINDSTRING("BOS",[Copy of Column 0],1)>0

    Order 2, [Copy of Column 0] == "BOS" || SUBSTRING([Copy of Column 0],1,500)=="BOS"

    The conditional split is not finding the string and the data goes to the default pipe.

    Thursday, September 06, 2012 1:26 AM
  • It appears to be something in the format of the flat file. I created a flat file with just BOS and it works as expected. So SSIS mustn't like a un-formatted flat file. Example of the file data.

    Processing Invoice: 8957[b]CRLF[/b] Processing LineItem: 1, Line Reference: , Excel Row: 2[b]CRLF[/b] The following Invoice: 8957 could not be processed due to An error has occurred intialising the BOS object. For further information please see the attached error.[b]CRLF[/b] The following LineItem: 1, Line Reference: , Excel Row: 2 could not be processed due to An error has occurred intialising the BOS object. For further information please see the attached error.[b]CRLF[/b] [b]CRLF[/b] Processing Invoice: 019[b]CRLF[/b] Processing LineItem: 1, Line Reference: , Excel Row: 3[b]CRLF[/b] The following Invoice: 019 could not be processed due to An error has occurred intialising the BOS object. For further information please see the attached error.[b]CRLF[/b] The following LineItem: 1, Line Reference: , Excel Row: 3 could not be processed due to An error has occurred intialising the BOS object. For further information please see the attached error.[b]CRLF[/b]

    [b]CRLF[/b]



    • Edited by Cameronh Friday, September 07, 2012 12:12 AM Added formating
    Thursday, September 06, 2012 1:34 AM
  • Anyone?
     I have the [b]CRLF[/b] to the sample of the file data, it is in a text file.
    • Edited by Cameronh Friday, September 07, 2012 12:12 AM
    Thursday, September 06, 2012 11:54 PM
  • problem could be with the column value.

    you are using value from [column] directly, that could have been the problem.

    try to save column values into some variables and use them into FINDSTRING function.

    Good Luck..


    Regards, Chirag Patel (ETL Engineer)

    • Marked as answer by Eileen Zhao Friday, September 14, 2012 6:21 AM
    Friday, September 07, 2012 1:47 AM