August 21, 2012 at 12:12 pm
I have a column in a Excel Spreadsheet with a code in it that is either a single digit number (0-9) or single letter(A-Z). The Letters come through to the SQL Table I am trying to populate and the numbers do not. Any one have any ideas on how to do this?
thanks
Steve
August 21, 2012 at 4:08 pm
Hi
I am new to this forum and would like to take a stab at this question.
With whatever info you have provided....
Please see first what SSIS is sending to your table. This you can do by putting in a DataGrid on the DataFlowPath (between the source and your destination) and observing the values. If you don't even see the values here, there may be a rpoblem with your excel.
i tried the same thing as you are trying to do and got all the values fine. I had the excel col formatted as 'general'.
Then, you can try seeing the definition table column and if at all theer are any kind of constratints on it which restrict digits from 0-9(highly unlikely as they would easily be converted to varchars)
Else
just play around with excel col formatting or add maybe a derived col and convert the digits to DT_WSTR
Hope this helps.
August 22, 2012 at 1:20 am
n79799 (8/21/2012)
HiI am new to this forum and would like to take a stab at this question.
With whatever info you have provided....
Please see first what SSIS is sending to your table. This you can do by putting in a DataGrid on the DataFlowPath (between the source and your destination) and observing the values. If you don't even see the values here, there may be a rpoblem with your excel.
i tried the same thing as you are trying to do and got all the values fine. I had the excel col formatted as 'general'.
Then, you can try seeing the definition table column and if at all theer are any kind of constratints on it which restrict digits from 0-9(highly unlikely as they would easily be converted to varchars)
Else
just play around with excel col formatting or add maybe a derived col and convert the digits to DT_WSTR
Hope this helps.
You may be new, but this is a good answer - the data grid idea is great advice.
One thing I would say is that the display format in Excel has no bearing on this, as it does not change the underlying data, just the display (obvious if you think about it).
I would also check the Advanced Editor for the data source & have a look at the data type which has been assigned to the problem column.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 23, 2012 at 5:13 am
you also may want to set the following:
in your connection to Excel : IMEX=1 --this is for mixed data types in the column
Secondly, check the TypeGuessRow Registristry setting....depending on whether the machines is 32 bit or 64 bit will dictate where this item is saved in the registry. Just do a find on TypeGuessRow. The default value is 8 which means that only the first 8 rows are reviewed to determine the data type of the data to be loaded. I would recommend a value much larger.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply