SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS, Excel, and DT_NTEXT


SSIS, Excel, and DT_NTEXT

Author
Message
Michael Lee-169622
Michael Lee-169622
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 237

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
SQL Adventurar
SQL Adventurar
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 111
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
Will Casey
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 181

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
Michael Lee-169622
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 237

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
SQL Adventurar
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 111
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
Frank Bazan
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 1087

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

  1. 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.
  2. 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.
  3. 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
RK-383011
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 240
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
Frank Bazan
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 1087
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
rparda
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 53
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
Harveysburger
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 430
FINALLY! Smile

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search