irb56
asked on
Sending DT_NTEXT data to Excel Destination in SSIS
Hi,
I need to output some string data from a couple of columns from a SQL Server OLE DB source into an Excel destination, where the source data type is nvarchar(max). I am using the template approach for the Excel destination, copying across the template and then using a SSIS data flow to pump the data in. My problem is that the external column metadata of the two columns defaults to DT_WSTR with length of 255. The trouble is that I am unable to change the external data type to DT_NTEXT. I try to set this via the Advanced Editor, exit the data flow but the change doesn't stick and it just goes back to DT_WSTR.
In the mappings of the Excel Destination, there is a mapping of DT_NTEXT from the input column (from the data flow pipeline) to DT_WSTR in the Excel external column. Consequently when I run the data flow it fails immediately on a validation error.
I understand from Microsoft that Excel only supports the following SSIS data types:
Numeric double-precision float (DT_R8)
Currency currency (DT_CY)
Boolean Boolean (DT_BOOL)
Date/time datetime (DT_DATE)
String Unicode string, length 255 (DT_WSTR)
Memo Unicode text stream (DT_NTEXT)
What I can't understand is how to make the two external columns be DT_NTEXT instead of DT_WSTR. Why does it ignore my change made via the Advanced Editor. Is there some other step required to ensure the column mapping is DT_NTEXT to DT_NTEXT?
Many thanks in advance.
I need to output some string data from a couple of columns from a SQL Server OLE DB source into an Excel destination, where the source data type is nvarchar(max). I am using the template approach for the Excel destination, copying across the template and then using a SSIS data flow to pump the data in. My problem is that the external column metadata of the two columns defaults to DT_WSTR with length of 255. The trouble is that I am unable to change the external data type to DT_NTEXT. I try to set this via the Advanced Editor, exit the data flow but the change doesn't stick and it just goes back to DT_WSTR.
In the mappings of the Excel Destination, there is a mapping of DT_NTEXT from the input column (from the data flow pipeline) to DT_WSTR in the Excel external column. Consequently when I run the data flow it fails immediately on a validation error.
I understand from Microsoft that Excel only supports the following SSIS data types:
Numeric double-precision float (DT_R8)
Currency currency (DT_CY)
Boolean Boolean (DT_BOOL)
Date/time datetime (DT_DATE)
String Unicode string, length 255 (DT_WSTR)
Memo Unicode text stream (DT_NTEXT)
What I can't understand is how to make the two external columns be DT_NTEXT instead of DT_WSTR. Why does it ignore my change made via the Advanced Editor. Is there some other step required to ensure the column mapping is DT_NTEXT to DT_NTEXT?
Many thanks in advance.
Aaron Shilo
you should try using a DataConversion trasformation
ASKER
I already use a data conversion, which sets the column in question to DT_NTEXT. It doesn't make any difference. The problem is that the Excel destination has decided the column I want to map to is DT_WSTR and nothing I do will change this. I've tried putting long text (over 255 characters) into the first 10 rows of the two columns of the Excel worksheet in question, then hiding those rows and recreating the Excel connector and destination again, but it makes no difference at all. The external columns of the Excel destination remain as DT_WSTR with length 255.
I'm going to try a different approach by creating the Excel file using Execute SQL tasks that use a CREATE TABLE statement and set the column data types in question to Longtext (as per Books Online). Hopefully this will work although it will sacrifice the benefit I get from the template approach (i.e. pre-setting the column widths to aid readability of the Excel worksheet post data input). I'll post an update with my findings in due course.
I'm going to try a different approach by creating the Excel file using Execute SQL tasks that use a CREATE TABLE statement and set the column data types in question to Longtext (as per Books Online). Hopefully this will work although it will sacrifice the benefit I get from the template approach (i.e. pre-setting the column widths to aid readability of the Excel worksheet post data input). I'll post an update with my findings in due course.
Hi,
Convert it to string using conversion task. and amp the converted column with destination column
Convert it to string using conversion task. and amp the converted column with destination column
ASKER
I have just recently achieved success by using an Execute SQL task to create the Excel workbook and worksheet. The following statement in this task will create a worksheet:
CREATE TABLE `MyReport`
(`id` nvarchar(255),
`amount` float,
`description` longtext)
When I do a data flow into the Excel destination that contains the worksheet named "MyReport" that was created by the aforementioned Execute SQL task, the metadata of the Excel external column is set to DT_NTEXT, which enables the mapping and data to flow in properly.
However, this method will only work with xls files (2003 and earlier), not with xslx. When I try it with xlsx, the metadata still stubbornly persists as DT_WSTR with length of 255. Is it only the older versions of Excel that recognise the longtext data type? Does anyone know of a different data type to specify in the Create Table statement that Excel 2007 will recognise as the SSIS database DT_NTEXT?
CREATE TABLE `MyReport`
(`id` nvarchar(255),
`amount` float,
`description` longtext)
When I do a data flow into the Excel destination that contains the worksheet named "MyReport" that was created by the aforementioned Execute SQL task, the metadata of the Excel external column is set to DT_NTEXT, which enables the mapping and data to flow in properly.
However, this method will only work with xls files (2003 and earlier), not with xslx. When I try it with xlsx, the metadata still stubbornly persists as DT_WSTR with length of 255. Is it only the older versions of Excel that recognise the longtext data type? Does anyone know of a different data type to specify in the Create Table statement that Excel 2007 will recognise as the SSIS database DT_NTEXT?
To irb56:
You sir are a real lifesaver.
You have not just resolved the issue but spoke about the most important tiny detail that could be easily overlooked (atleast i did) when you look at the error from ssis. THE FILE TYPE !!.
Guess what .. I was already doing everything that you had mentioned in your comments. All the data conversion and derived data column tasks. NOTHING WORKED!!
I had the exact same issue. Browsed the whole internet for a day looking for the answer and you have exactly and i must say precisely pointed out the issue.
The issue was the excel file extension itself. I was using an .xlsx file. Damn thing always used to revert back to DT_WSTR when i was using it in the excel destination task.
Here is what i was doing first:
i had an empty .xlsx file that i am using as an template. I am creating the target table for data load from the ole db source within the package itself. I was using the longtext datatype for the column which will hold the huge data string but still the package used to throw me an error of [Excel Destination [91]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
I checked the datatype (external, in/out) .. even at the excel destination task. I corrected the datatype for the external column in excel destination task but then as soon as i exit the DFT, it was getting reversed. i had no idea why .. NTEXT maps to MEMO so i even tried to create the excel column with the datatype memo. Still No Go!!
Then i read your post. It was indeed the excel sheet itself.
I will try and find the answer for the question that you had. Why .xlsx acts wierd with the input DT_NTEXT value.
Thanks a million for the wonderful tip.
-
John
You sir are a real lifesaver.
You have not just resolved the issue but spoke about the most important tiny detail that could be easily overlooked (atleast i did) when you look at the error from ssis. THE FILE TYPE !!.
Guess what .. I was already doing everything that you had mentioned in your comments. All the data conversion and derived data column tasks. NOTHING WORKED!!
I had the exact same issue. Browsed the whole internet for a day looking for the answer and you have exactly and i must say precisely pointed out the issue.
The issue was the excel file extension itself. I was using an .xlsx file. Damn thing always used to revert back to DT_WSTR when i was using it in the excel destination task.
Here is what i was doing first:
i had an empty .xlsx file that i am using as an template. I am creating the target table for data load from the ole db source within the package itself. I was using the longtext datatype for the column which will hold the huge data string but still the package used to throw me an error of [Excel Destination [91]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
I checked the datatype (external, in/out) .. even at the excel destination task. I corrected the datatype for the external column in excel destination task but then as soon as i exit the DFT, it was getting reversed. i had no idea why .. NTEXT maps to MEMO so i even tried to create the excel column with the datatype memo. Still No Go!!
Then i read your post. It was indeed the excel sheet itself.
I will try and find the answer for the question that you had. Why .xlsx acts wierd with the input DT_NTEXT value.
Thanks a million for the wonderful tip.
-
John
ASKER
Hi John,
I'm pleased to hear you found this thread useful, and thanks for the nice comments. If you happen to find an answer to the question of DT_NTEXT columns in a SSIS load of an xlsx file, please share. SSIS is a bit limited I think in respect to working with Excel files, which I find a little surprising considering it's all Microsoft technology.
Ian
I'm pleased to hear you found this thread useful, and thanks for the nice comments. If you happen to find an answer to the question of DT_NTEXT columns in a SSIS load of an xlsx file, please share. SSIS is a bit limited I think in respect to working with Excel files, which I find a little surprising considering it's all Microsoft technology.
Ian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Forced to close question before I can ask any more!
Seems like a silly problem, but for anyone working with Excel 2007 files and SQL Server 2012 BI, putting a bunch of text in the first row of your sheet, and then hiding that row forces the integration package to load the column metadata as dt_ntext instead of dt_wstr(255). I guess this never used to work, but worked fine for me. Seems like a silly workaround still, not very intuitive or obvious, but at least it works.