December 17, 2015 at 5:26 am
Hi all,
I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!
December 17, 2015 at 5:59 am
murtzd (12/17/2015)
Hi all,I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!
What data types have you tried for the SSIS 'ValuationDate' variable?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 17, 2015 at 6:05 am
All of them! (some are obviously not fit for purpose but I had to try because of no other option)
December 17, 2015 at 6:52 am
OK, then I am not sure I can help.
As Excel holds dates as integers (try formatting your date as 'General' and you will see what I mean), I would have expected you to be able to import to an I4 variable.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 17, 2015 at 7:03 am
That is the main problem. I need to read this value into SSIS to eliminate any manual intervention on the Excel file itself. The file gets imported into a folder overnight after which the SSIS process imports it into the database. If I was to manually change the Excel file, this automated process would be impossible.
The value in the Excel cell is formatted as a Date. I would have thought it was straightforward to have it imported into a SSIS variable that was of datatype Datetime or even String 🙁
December 18, 2015 at 3:54 am
Anyone? 🙁
December 21, 2015 at 2:28 am
:unsure:
December 21, 2015 at 3:01 am
when you have added date column in the excel, Is the date column is show left align data or right aligned ?
December 21, 2015 at 3:09 am
It's right aligned (although it doesn't actually show the right aligned button as being selected) and bottom aligned..
December 21, 2015 at 3:40 am
i never recommend reading excel like this. if you want to read excel and want to avoid such issues you should be using script task in which you can write c#/vb.net code of your liking and get any kind of data you want. you will always get the date issue in your current working.
something like this
December 22, 2015 at 3:41 am
Ok thanks. I'll give this route a go!
December 24, 2015 at 5:56 am
I agree with twin.devil
but I use vba in Excel. Search google like
"vba insert sql excel"
January 11, 2016 at 6:18 am
I tried to google VBA solutions, but because I am a total novice when it comes to VBA, I couldn't make any headway at all.
Can anyone give me a starter please?
January 11, 2016 at 6:56 am
have you tried storing the date value in the table then converting it to correct datatype ?
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
January 14, 2016 at 4:56 am
I don't understand what you mean? What table?
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply