Random Findings as a Developer

May 4, 2012

SSIS and VARCHAR(MAX) / NVARCHAR(MAX)

Filed under: SSIS — Andrew @ 7:26 pm

Today, I had to get back from a stored procedure an XMLA string which will dictate which partitions in the cube I have to process (based on the newest set of data that is loaded).

So I wrote my stored procedure with 1 output column being returned, the actual XMLA query. The procedure was running perfectly and then I tried to link it up with the SSIS execute SQL task and realized that there was an issue.

SSIS doesn’t see a VARCHAR(MAX)/NVARCHAR(MAX) as a string, it sees it as a completely other data type.

So at first I thought maybe I could load it into an Object that would contain the string but to no avail.

Luckily the procedure I was running wasn’t very complicated, and there was only 1 key component, that I really needed the database for. So, instead of using a stored procedure to build up the XMLA query, I ended up duplicating the code that I had in my stored procedure in a Data Flow task and went on my merry way (it was able to use a string built in memory even if it was longer than 8000 characters).

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress