August 24, 2012 at 4:16 am
Hi,
Has anyone ever tried using a column with dt_ntext datatype in the pivot transformation for ssis 2008?
I am trying to use it and it gives me the following error -
Error at DFT - Load and Pivot DAta [Pivot[[40]]: The Input column "DataSubmitted" (2818) cannot be used as a Set Key, Pivot Key or Pivot Value because it contains long data.
If i use DT_WSTR it works fine but I need to use Dt_Ntext as length of the incoming data is not defined and I need to scope that in my ssis package.
Any help would be highly appreciated.. I am literally stuck here
Thanks..
August 27, 2012 at 1:41 am
Will the length ever be greater than 4000?
If not, you can just take DT_WSTR with a length of 4000.
Not great for package performance, but using a PIVOT isn't either 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2012 at 1:45 pm
I cannot use dt_Wstr because the length is expected to be more indefinite, which definitely can be 4000 characters. I think my only out with this will be to write some sql code to pivot the data rather than using the ssis pivot transformation. But it really is strange that SSIS pivot transformation doesn't support dt_ntext datatype 😕
August 27, 2012 at 11:33 pm
As long as the length is not more than 4000 characters, DT_WSTR shouldn't be a problem. Varying length is not an issue.
DT_NTEXT is BLOB (or CLOB, not sure) data, so it has all kinds of restrictions. Most likely because it's not that easy to inspect it's contents.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply