August 2, 2010 at 11:30 am
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
August 2, 2010 at 12:50 pm
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]
August 2, 2010 at 10:04 pm
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
August 3, 2010 at 10:39 am
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]
August 3, 2010 at 12:27 pm
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...:-)
August 3, 2010 at 12:34 pm
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]
August 4, 2010 at 12:34 pm
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.
August 4, 2010 at 1:00 pm
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]
August 4, 2010 at 1:17 pm
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
August 4, 2010 at 1:17 pm
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
August 4, 2010 at 1:30 pm
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]
August 4, 2010 at 9:38 pm
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
August 10, 2010 at 5:38 am
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 knowI 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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply