Racsco


Hi

I am after some help with the following issue

I have a package that reads a table for a file path of a excel file. This gets passed to a variable and then this file is imported into a staging table for further transformation work. The issue i have is that file 1 may contain data in Column A which is 50 characters long in which case i have to import the excel as a DT_WSTR, do a data conversion to a DT_STR and load to the staging table. However file 2 may contain data in Column which is over 255 characters in which case it would import as a DT_NTEXT which i then transform to a DT_TEXT and then to a DT_STR. I used a fixed file path in the Excel Connection to start with which was for File 1 so the datatype for column 1 is a DT_WSTR. I then changed the excel connection to a filepath variable, put the path of file 2 in my table and called it from my package. It failed as the data exceeding 255 characters in column 1 needed to be a DT_NTEXT. I can change it and it works but if i then run the package using file 1 (less than 255 characters) it fails again as it wants it to be a DT_WSTR.

Is there anyway around this Am i missing something as i would have thought that by setting it to DT_NTEXT this would cover data under 255 characters as well.

Any help is gratefully aprreciated.




Re: Excel Source from Variables and data type Issue

jwelch


DT_WSTR and DTNTEXT are different types, so SSIS detects this as a metadata change. It's based on what the Excel provider reports back to SSIS.

Someone ran into to this a few months ago, and I think they ended up having to use two different data flows, one for DT_WSTR and one using DT_NTEXT.







Re: Excel Source from Variables and data type Issue

Racsco

hi

Thanks for the reply.

I have it kind of working using your suggestion here. I now have 2 data flow tasks, the first is for the troublesome column as a DT_WSTR (the most common for the files imported) and an on fail constraint to a NTEXT. In theory it works fine however because the first component fails when it is an NTEXT the whole package fails because it is within a foreach loop.

as far as I can see the only solution would be to either increae the Maximum number of errors on the foreach loop which will cause a problem if something else outside of these 2 components fails or to change the error handling on the dataflow task. I cannot ignore the error on truncate in the dataflow task as it will suceed but will import only the first 255 characters using the DT_WSTR flow. If I cange the onerror erro output it still appears to record the error against the error count so the max error count is reached.

Any suggestions would be greatly received

Thanks again






Re: Excel Source from Variables and data type Issue

jwelch

Could you use an Execute SQL or Script task to query the metadata for the Excel sheet ahead of time, and redirect to the appropriate task This link has more information on using GetOleDbSchemaTable to get this type of information.

http://support.microsoft.com/kb/309488