locked
Retrieve specific cell value from Excel into SSIS variable RRS feed

  • Question

  • 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!!

    Wednesday, December 16, 2015 5:01 PM

All replies

  • Hi Murtz,What do you mean changing to datatype date? Do you make it formatted like date or you specify the cell formatting as date?

    Arthur

    MyBlog


    Twitter

    Wednesday, December 16, 2015 6:45 PM
  • Hi,

    I would tried to change Data type of variable "ValuationDate":  to String and see how it works.

    Regards,

    Balwant.


    Failure in Life is failure to try... PGDCA-98(South Gujarat University),MCSA (SQL 2012) , MCTS (SQL Server 2005) http://blog.vspatel.co.uk/

    Thursday, December 17, 2015 8:56 AM
  • I mean I changed the Variable data type in SSIS to every possible type. I have tried Datetime, and string also, but nothing works.

    I'm unable to change the data type for the cell B4 in Excel due to this adding manual intervention. I need this process to be completely automated.

    Thursday, December 17, 2015 11:22 AM
  • Anyone??
    Friday, December 18, 2015 10:53 AM
  • ???? 
    Monday, December 21, 2015 9:29 AM
  • Scenario : 

    We have an excel file with Sheet1. We need to get the CreatedBy value that exists in C3 Cell. After reading this information we can use against our validation table to find out if it matches with our requirement so we can proceed with loading Or we can read this information and insert into our Audit table for our record. In short we will be learning how to read a Cell value from Excel in SSIS.
    Our input file look like this

    Solution : 

    We will be using Execute SQL Task in SSIS Package to perform this and Script task to display the value.
    Step 1: 
    Lets create a variable with name VarCreatedBy as shown below
    Step 2:
    Place Execute SQL Task on Control Flow Pane and Configure as shown below. Double Click on Execute SQL Task and then Choose Excel in ConnectionType and Click next to Connection that will lead to Source File.
    Step 3:
    Write Query as shown below and map the Result Set to VarCreatedBy. As you can see in query even I am reading C3 , I did not use C3:C3. we have to provide the range C3:C4 ( means read C3 cell value).
    Map the variable
    Step 4: 
    Lets check the value of VarCreatedBy variable by using Script task
    Write the highlighted code as shown below in Script task to display the value at run time.


    Final Output :
    Lets run the Package and see the final output. As we can see it read the Cell value (C3) and displayed
    Monday, December 21, 2015 12:44 PM
  • Thanks for this. This works for me also, however I am having a problem when there is a date in the Excel field. Text has always worked fine.
    Tuesday, December 22, 2015 10:41 AM
  • Help :)
    Wednesday, December 23, 2015 11:26 AM
  • What is the problem with the date?

    Arthur

    MyBlog


    Twitter

    Wednesday, December 23, 2015 2:21 PM
  • Thanks for this. This works for me also, however I am having a problem when there is a date in the Excel field. Text has always worked fine.
    I would recommend you check the commercial COZYROC Excel Task. It includes action to read Excel cell into a variable.

    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Thursday, December 24, 2015 7:36 PM
  • Thanks for that, but I'd prefer a solution without paying for a third party product.

    Can anyone help please? :(

    Monday, January 11, 2016 1:17 PM
  • What problem occurs when it is a date? What error? Parsing the date?

    If it works with the string, convert it to string.


    Arthur

    MyBlog


    Twitter

    Monday, January 11, 2016 5:50 PM
  • An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"

    That's the error I get. It seems that the value is not set to the variable if the excel cell format is Date. I can't convert it to string because I don't want to open the file and manually modify it. I want this all to be automated.


    Thursday, January 14, 2016 11:55 AM
  • When you inspect the data type from Excel, what it is? And how would I be able to reproduce this issue?

    I am asking because the error says you possibly executed a query against Excel and mapped the returned value to an incorrect datatype so we need to see what you did please


    Arthur

    MyBlog


    Twitter

    Thursday, January 14, 2016 2:28 PM
  • The data format in Excel is DATE

    You can reproduce the error by creating a spreadsheet with the value '01/10/2015' in cell B4, and follow the post by Andrey Langovoy, but replacing the SQL Statement with SELECT * FROM [Sheet1$B4:B5]

    Thursday, January 14, 2016 2:33 PM
  • Then convert the value to string in the SQL as SELECT a, b, CAST(thedate as VARCHAR(50)) as mydate FROM [Sheet1$B4:B5]

    Arthur

    MyBlog


    Twitter

    Thursday, January 14, 2016 3:24 PM
  • Then convert the value to string in the SQL as SELECT a, b, CAST(thedate as VARCHAR(50)) as mydate FROM [Sheet1$B4:B5]

    Arthur

    MyBlog


    Twitter


    The question is about querying Excel and CAST is not a function available in the query language we can issue to Excel over the ACE or JET Drivers.
    Thursday, January 21, 2016 4:46 AM
  • This thread comes up on Bing when searching for a way to capture the contents of a cell with a date in it and nowhere else I could find specifically solved it. Here is a solution:

    http://www.sqlservercentral.com/Forums/Topic1745924-364-2.aspx#bm1754373


    Thursday, January 21, 2016 4:47 AM
  • Hey!

    First thanks for this great solution. Works like a charm when I forward it to a Script Task.

    But when I try to get it into a OLE DB or Excel I always get an error that the value could not be converted because of potential loss of data.

    Do you have any idea why?

    Thanks in advance 

    Thursday, December 8, 2016 9:25 AM
  • Hey!

    First thanks for this great solution. Works like a charm when I forward it to a Script Task.

    But when I try to get it into a OLE DB or Excel I always get an error that the value could not be converted because of potential loss of data.

    Do you have any idea why?

    Thanks in advance 


    Sounds like you want the scalar value to come put of a Data Flow Source Component. Can you explain more about what you're trying to do? Screenshots of your Control and Data Flow tabs might help too.
    Thursday, December 8, 2016 9:44 AM
  • Thanks for the solution it worked but what i am trying to do is pull multiple value from 1 excel sheet and i want to do it in one script task or sql task editor. How would I go about doing it?
    Tuesday, November 21, 2017 3:59 PM