Post reply

Retrieve specific cell value from Excel into SSIS variable

  • January 21, 2016 at 11:41 am

    #1853598

    Phil Parkin (1/21/2016)

    MMartin1 (1/21/2016)

    Brilliant work Orlando. It makes sense that the execute sql task is returning the results of a SELECT. That will be in the form of a result set, even if it is a one row one column result. Hence the need of a Object type variable.

    I will second this sentiment: great work, Orlando.

    Not sure I agree with the 'makes sense' comment though. The 'Single Row' ResultSet option should also work, IMO.

    Right, my thinking now is that Excel VBA Range operator defines one or more cells. I am thinking the Excel connection manager, for simplicity in trying to hook to the Range, just makes use of the full result set- not knowing what its going to get. Not that this could not be improved to make use of the Single Result Set, still.

    ----------------------------------------------------

  • January 25, 2016 at 4:12 am

    #1854114

    I still don't understand how this works logically though. The original method did it's job in returning a single cell value to a SSIS variable. However it just didn't work when the value was formatted as a date!

    How does this even make sense? :crazy:

  • January 26, 2016 at 3:08 am

    #1854383

    This likely won't be an explanation that completely satisfies your technical curiosity but I will make an attempt. If we could drag an SSIS Team developer and an ACE Driver Team developer in here I am sure this could be explained at a more technical level but I am not privy to those kinds of details.

    What I can say is that there are numerous examples where SSIS cannot map data types from certain data sources to its own type sub-system. The list of SSIS types is finite and must be rigid and it attempts to allow us to store data from any other type system in them. The mismatch of external types to SSIS types occurs more often when interacting with non-Microsoft tools (e.g. Oracle NUMBER is a big one that causes problems) but it happens with Microsoft tools as well.

    The Access/Excel Driver team is different from the SSIS team and one might think it is a priority for both to get together and make sure their stuff integrates with each other but alas, it is not always the case.

    Other cases come to mind even within the SQL Server space. Attempting to map a SQL Server XML column or MAX column to an SSIS variable of type String using an Execute SQL Task is also a futile effort and requires some level of effort to work around.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 46 through 47 (of 47 total)

You must be logged in to reply to this topic. Login to reply