June 27, 2016 at 3:34 pm
I have to import 6-7 xlsx files into the SQL Server. These files are created by exports from another application. Each of the file has several thousand rows and 20 to 50 columns. And I need to do this task quite frequently. And generally, a lot of null values in several columns and rows. I do not have control over the format of the export data.
I set up the SSIS data flow tasks for each of these and now I get an error as below:
Invalid character value for cast specification.
I searched and it seems I have to modify the files to make sure that the format is correct. But this could happen in multiple places and in any of the files that I need to load. So if I have to correct the data before running the SSIS package, why would I not directly import into the DB using the import/exporet wizard?
Am I doing something wrong? Or the effort is worthless?
Thanks for your help in advance.
June 29, 2016 at 4:54 am
Importing data from Excel is often problematic.
One of the main reasons is that by default the datatype of each column is based on the data in the first 8 rows only so if you have a column of mixed numeric and character if the first 8 rows are numeric then the whole of the column will be treated as numeric and character values may cause a failure or be represented as null.
Is your Data Flow Task failing when it is reading from the Excel file or is if failing somewhere else? If the Excel source component turns red then it is failing when reading from Excel.
Jez
July 11, 2016 at 3:06 am
Not sure how you would do that with the Import/Export wizard.. Even if you manage to (assuming excel is still the source) you'll run in to the same error again as Import/Export wizard uses SSIS data flow under the hood..
This error, although, is very common while loading flat files when the row delimiter occurs somewhere in the data rather than at the end of a row..
For excel source though it can't be the problem as data is contained in cells..
Have you tried redirecting the error rows into another component (you can use the conditional split for example) and enable the data viewer to check which rows have problems..
At the very least this will narrow down the rows with error and make it easy for you to debug in the data viewer itself..
July 11, 2016 at 3:36 am
Is the format of your excel documents likely to change? Also is each one in the same format?
I have a task I run at work that reads 5 Excel Worksheets in a single workbook, that are all the "same" format. Unfortunately, like Jez said, some fields are misinterpreted by the JET Engine, and are assumed to be numbers when they are string, dates when they are numbers, and all kinds of things. And it varies from worksheet to worksheet as to what it thinks they are.
As a result, I use a script Component with an Excel Reader to actually get all my data. It's not that quick to write at first, but if you're data is always going to be in the same format(and especially if all your worksheets are), then this will get around your data type problems. If your sheets are likely to change, or frequently do, his is not the solution for you though.
If they are static though, then I'm happy to provide detail on how to achieve this with a Script Component, but i don't want to muddy the water if they're subject to change.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 12, 2016 at 12:15 am
Thanks Old Hand, all files for the same data set come in the same format. So there would be 6 -7 files - each a different data set. But each of these files have to be reloaded bi-monthly with revised data.
I will try to debug and see what I find. Thanks for the pointer.
Else I may just get everything as varchar and then convert in a separate step, in case some numbers are being imported as varchar.
Thanks for your help.
July 12, 2016 at 12:17 am
Thanks, that maybe hte problem. I will try to load all fields as varchar and see what happens.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply