SSIS, Excel, and DT_NTEXT

Question

SSIS, Excel, and DT_NTEXT

Michael Lee-169622

SSCommitted

Points: 1721
More actions
March 7, 2007 at 2:04 pm

#119172

OK, I have fought this problem for the last three days. I now bow to the extensive and superior knoweledge of this forum for help.
My problem KISSed to the max: I have a varchar(1000) column in a SQL table called OrderComments. I need to export it to an Excel file.
I have built a SSIS package with a Data Flow task.
The OLE DB Source External Columns has OrderComments as string[DT_STR] with a length of 1000. The Output Columns has OrderComments as string[DT_STR] with a length of 1000.
The output flows to a Data Conversion component. The Input Columns has OrderComments as DTR_STR with a length of 1000. The Output Columns has 'Copy of OrderComments' as Unicode text stream[DT_NTEXT].
The output from the Data Conversion component flows to an Excel Destination component. The Input Columns has 'Copy of OrderComments' as DT_NTEXT. This is mapped to s External Columns named OrderComments with a data type of Unicode String [DT_WSTR] with a length of 255.
I created an Excel file template with only the column name in the first row. No other formatting or anything is done to the file.
I cannot change the External Column in the Excel Destination to a DT_NTEXT, nor can I delete and re-add the column. It always converts to a Unicode String [DT_WSTR] with a length of 255.
Oh, please, great and wonderful Gurus of Mr. Bill, do you have any suggestions? I gotta be missing something elemental.
Thanks in advance,
Shalom!,
Michael Lee

Viewing 13 posts - 1 through 12 (of 12 total)

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

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 1

Have you tried to run a "execute SQL task" against the excel connection. I use this to create the excel sheets inside the file and mod the datatypes where neccesarry.

The syntax is verly similar to SQL:

DROP TABLE `Sheet_name`

go

CREATE TABLE `Sheet_name` (

`column1` VARCHAR(10),

`column2` VARCHAR(50)

)

The connection type is "Excel" The the connection property is the excel sheet?

Hope this helps

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 2

Michael,

What I do in a smiliar situation is output data to a flat file Out.txt and then rename the txt using either a script or a file system task and it works consistently.

W

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 3

Amazing! I KNEW Will's answere would not work, come on, just rename a file? But it really worked! Thanks Will!

For my own edification, and because is seems more "professional", I would like to understand Jacques answer a little better. I tried to apply a SQL task against the Excel connection, but the only selections I had available were for an OLE DB connection, which I could not figure out how to apply to an Excel File. Jacques, Could you please elaborate on this?

This forum is awesome! Thanks to all!

Shalom!,

Michael Lee

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 4

Hi Michael

I find it odd that you can not see the excel connection type.

If I may elaborate on the way I did the excel connections. When I worked on SQL2000 I read a document which showed a way to connect to Excel using a SQL statement connections. This same method I applied to SSIS.

In the Dev studio I create an Excel connection to the excel file. Once this is done I do set up an import to the excel file. (This should prompt you to create a excel table) The script used to create the excel table I then copy to the sql statement task later. Create the excel sheet and export the data in the data flow task.

I then exit the data flow task and in the control flow you create a "Execute SQL task" object. Within this object it gives me the option under the connection type to use Excel. (I know that the "Execute T-SQL statement" object does not give you the excel option) Once you have selected the Excel connection you can select the excel sheet enter the SQLstatement by "paste"'ing the create script add the drop table line at the top and all should be working.

Hope this help.

Jacques

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 5

The connection manager for excel uses the Excel Jet Driver. What this does when you create your source and destinations is sample the first 8 records and based on what it finds in the column sets your external columns accordingly.

Even if you have set the column lengths in the Excel file using SQL, you may still find that SSIS fails where the data in your spreadsheet isn't of a length that you've specified (i.e. If you've set a column to be DT_NTEXT yet in your spreadsheets first 8 rows there are no column lengths longer than 255 characters).

There are options to deal with this

If you are going to be writing to a new Excel file each time, create a template which has a dummy row as the first record which contains enough characters for your datatype.; copy it to a new file; and make this your destination each time.
If you are appending to an existing file either a) Alter the Registry setting so that it samples more records. or b) put one record that does meet the specified length criteria (>255 characters) as the first record.
A note about changing the registry - this must be done on all machines that are going to run the package as otherwise it will work fine on your machine, but at runtime a server running it will fail.

Issue & registry config instructions in slightly more detail

Kindest Regards,

Frank Bazan

RK-383011 Old Hand Points: 313 More actions · Answer 6

In response to Jacques post on 3/8/2007:

When I use the following create statement, it gives the error "Size of field column3 is too long". It does not solve the core issue of being able to create a field greater than 255 chars.

CREATE TABLE `Sheet_name` (

`column1` VARCHAR(10),

`column2` VARCHAR(50),

`column3` VARCHAR(1000)

)

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 7

Not sure if you found an answer to this or not, but if you want a datatype bigger than 255 characters, you use MEMO as your datatype. This maps to DT_NTEXT SSIS datatype.

Kindest Regards,

Frank Bazan

rparda Valued Member Points: 71 More actions · Answer 8

The problem is that when the JET engine pulls in the Excel spreadsheet it will (by default) read just the first 8 rows to determine what the column datatype should be. If the row that has the +255 entry is after row 8 JET will pick the DT-WSTR (255) datatype. You have two methods of solving your problem.

1. either move the row with the +255 data column to become one of the 1st 8 rows.

2. go to the following link and read how to change your Excel registry to read more than 8 rows. http://support.microsoft.com/kb/281517

Harveysburger SSCommitted Points: 1562 More actions · Answer 9

FINALLY! 🙂

I just wasted tons of time on this problem... but I see what my mistake was...

I also had a problem where when exporting to Excel all my data types would always get converted to 255 characters unicode string (WSTR). This was despite the fact that everywhere else in my package the data types used were money, int, nvarchar(10), etc...

I generated a package using the wizard to see what the wizard is doing differently then I was (cuz with the wizard everything works fine)

The problem was with how I was creating the excel sheet (aka destination table) in the 'Excel Destination' component.

My code was

CREATE TABLE `Before7AM` (

`CustomerID` NVARCHAR(10),

`TranCount` INTEGER,

`TotalVolume` MONEY

)

But for some reason I had to change the code to this for it to work:

CREATE TABLE `Before7AM` (

`CustomerID` LongText,

`TranCount` Long,

`TotalVolume` Currency

)

Also I had to move the creation of the destination table out of the 'Excel Destination' component to a separate 'SQL Task' component. Not sure why... So it seems the problem was mismatch of data types names...

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 10

I have written an aricle on this. Kindly check it.

http://venkattechnicalblog.blogspot.com/2008/09/excel-column-is-greater-than-255-in.html

Regards,

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/2008/09/excel-column-is-greater-than-255-in.html

Regards,

Venkatesan Prabu .J

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

arun4.g SSC Enthusiast Points: 193 More actions · Answer 11

If you want to create a cplumn that has more than 255 characters, you can use TEXT as its datatype (not sure if it works for more that 8000 Characters).

For eg. While creating Table in Execute SQL Task, you code looks somewhat like this:

Create Table 'Table Name' {

'Column1' Varchar(80),

'Column2' TEXT

}

When you are writing this column to Excel File, you need to use a Data Converstion Task inside Data Flow Task and change the column type to Unicode Text Stream

kjjaison SSC Journeyman Points: 91 More actions · Answer 12

Hi Frank Bazan,

Thanks for your post. This is very helpful to me

Thanks

Jaison