Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS 2008 - Pivot transformation and DT_NText Datatype Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 4:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:30 AM
Points: 31, Visits: 105
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..
Post #1349554
Posted Monday, August 27, 2012 1:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 13,735, Visits: 10,706
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1350239
Posted Monday, August 27, 2012 1:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:30 AM
Points: 31, Visits: 105
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 :?
Post #1350557
Posted Monday, August 27, 2012 11:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 13,735, Visits: 10,706
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1350689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse