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).