SSIS Script Task and running a stored procedure

Question

SSIS Script Task and running a stored procedure

Viewing 3 posts - 16 through 17 (of 17 total)

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

JustMarie SSCertifiable Points: 7771 More actions · Answer 1

I still got truncation using the output to NVARCHAR. I'm surprised that your testing didn't show the same thing unless you didn't butt up against the limit.

Since I have to script sending HTML formatted email it's no big deal to add the piece for running through an ADO object.

FYI - These emails contain a table with all the entries in an audit log when there's some kind of critical event. Given that I log almost everything there can be a lot of rows. The fun of ETL work. 😛

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

JustMarie (2/3/2016)
I still got truncation using the output to NVARCHAR. I'm surprised that your testing didn't show the same thing unless you didn't butt up against the limit.
Since I have to script sending HTML formatted email it's no big deal to add the piece for running through an ADO object.
FYI - These emails contain a table with all the entries in an audit log when there's some kind of critical event. Given that I log almost everything there can be a lot of rows. The fun of ETL work. 😛

My fault. I uploaded an incorrect picture in my earlier post and just uploaded a new version. In the Mapping page of the Execute SQL Task you have to change the Parameter Size from default of -1 to an appropriately large size to accommodate your data, otherwise the cutoff is 8192 characters. In my example I have it set at 2 million and successfully tested strings coming back from the database up to ~1.9 million characters in length.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

JustMarie SSCertifiable Points: 7771 More actions · Answer 3

Yup! That did it for not having to code the ADO Object.

My test data is 12,460 characters. I don't see using the whole 2mil but I'm not complaining in the slightest!

Thanks again for all your effort in putting together the solution. Hopefully this will prevent other people from having to hunt for solutions. We've got 2 options there and choice is good.