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 12»»

SSIS, Excel, and DT_NTEXT Expand / Collapse
Author
Message
Posted Wednesday, March 07, 2007 2:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 12:42 PM
Points: 77, 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
Post #349843
Posted Wednesday, March 07, 2007 10:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 2:21 AM
Points: 32, Visits: 95
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

Post #349906
Posted Thursday, March 08, 2007 3:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 4:28 AM
Points: 97, Visits: 179

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

 

 

Post #349949
Posted Thursday, March 08, 2007 8:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 12:42 PM
Points: 77, 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
Post #350053
Posted Thursday, March 08, 2007 11:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 2:21 AM
Points: 32, Visits: 95
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
Post #350255
Posted Friday, March 09, 2007 6:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 28, 2014 4:38 AM
Points: 268, Visits: 1,072

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
Post #350332
Posted Thursday, February 07, 2008 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:54 PM
Points: 13, Visits: 233
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)
)
Post #452849
Posted Sunday, March 23, 2008 5:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 28, 2014 4:38 AM
Points: 268, Visits: 1,072
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
Post #473298
Posted Friday, April 25, 2008 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 12:43 PM
Points: 1, Visits: 39
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
Post #490913
Posted Wednesday, May 21, 2008 12:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 4:03 PM
Points: 173, Visits: 422
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...
Post #504736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse