How to Read TEXT data and emailing

  • I would like to know how to read TEXT data from database, i wanted to send this via email (txt file)

    I tried to read using READTEXT with pointer, but not able to retrieve all the data which is about 30000 characters, as pointer size is varbinary(16)

    also tried XML, but it contains some invalid characters such as <, some hash #, etc

    is there any easy method? to read exact data as it is...thnx

  • Did you try Substring function?

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Baskar B.V (8/2/2010)


    Did you try Substring function?

    Substring is not returning more than 8000 chars, i wanted to read 30000 chars data. tks

  • Substring is not returning more than 8000 chars, i wanted to read 30000 chars data. tks

    Is it possible to push the data value to a .txt file temporarily and use it as messagesource for the "send mail" task?

    Other option if the string is always around 30000 chars, we can split the contents using multiple substring function and use the variable in send mail task. But i am not sure whether multiple variables can be embedded using "+" operator in the mail task.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Baskar B.V (8/3/2010)


    Substring is not returning more than 8000 chars, i wanted to read 30000 chars data. tks

    Is it possible to push the data value to a .txt file temporarily and use it as messagesource for the "send mail" task?

    Other option if the string is always around 30000 chars, we can split the contents using multiple substring function and use the variable in send mail task. But i am not sure whether multiple variables can be embedded using "+" operator in the mail task.

    Thanks for your reply, option 1 is an alternative solution, however my issue was resolved...:-)

  • Thanks for your reply, option 1 is an alternative solution, however my issue was resolved...:-)

    Please let us know how you resolved this issue so that it would be useful for people who has similar issue.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Baskar B.V (8/3/2010)


    Thanks for your reply, option 1 is an alternative solution, however my issue was resolved...:-)

    Please let us know how you resolved this issue so that it would be useful for people who has similar issue.

    I dont have code at home (not allowed @office), but what I did was...

    Based on Biz rules, i retrieved less than 5K data each iteration and sent that max position value as input to next iteration and so on

    sent all output to txt file.

  • PaVeRa22 (8/4/2010)


    Baskar B.V (8/3/2010)


    Thanks for your reply, option 1 is an alternative solution, however my issue was resolved...:-)

    Please let us know how you resolved this issue so that it would be useful for people who has similar issue.

    I dont have code at home (not allowed @office), but what I did was...

    Based on Biz rules, i retrieved less than 5K data each iteration and sent that max position value as input to next iteration and so on

    sent all output to txt file.

    Thnx. So you used the approach 1 for the solution. I thought you used in built function similar to substring to get this done. Hope there is no method to directly read "text" data into a string variable or so.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • Do you want to read the Complete data in that field or just the 30000 characters ? If you want to read complete field i might be able to help you..Let me know

  • Thnx. So you used the approach 1 for the solution. I thought you used in built function similar to substring to get this done. Hope there is no method to directly read "text" data into a string variable or so.[/quote]

    I tried both READTEXT and Substring options, and other methods too, like xml... but finally using Substring in 2005

    however, in 2008, for XML AUTO is returning all data

  • divyanth (8/4/2010)


    Do you want to read the Complete data in that field or just the 30000 characters ? If you want to read complete field i might be able to help you..Let me know

    yes. How to read the entire contents of "text" data type into a SSIS variable or so?

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

  • divyanth (8/4/2010)


    Do you want to read the Complete data in that field or just the 30000 characters ? If you want to read complete field i might be able to help you..Let me know

    I have 6,50,000 chars in the same field of TEXT data type(2K5), i wanted to read and email that data, my logic is working for this as well, if u have any diff solution, u can post here, tks

  • PaVeRa22 (8/4/2010)


    divyanth (8/4/2010)


    Do you want to read the Complete data in that field or just the 30000 characters ? If you want to read complete field i might be able to help you..Let me know

    I have 6,50,000 chars in the same field of TEXT data type(2K5), i wanted to read and email that data, my logic is working for this as well, if u have any diff solution, u can post here, tks

    Why not use the standard Export Column component from SSIS ? You don't need to write script to export the entire content of a column.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 13 posts - 1 through 12 (of 12 total)

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