Weird problem with data flow task

  • I've got a data flow task where the source is a hard-coded parameterized query, i.e.

    SELECT ColA, ColB, ColC

    FROM TABLE_DTL

    WHERE LastModifiedNumericDate > ?

    (All of the dates are stored as integers, i.e. 20140412.)

    The value for the parameter is 0 (zero) and should return every row but it isn't returning anything although it is "successful". If I cut and paste the query into a query window and substitute 0 for the ? it returns every row.

    When I run the package through the debugger and break at that data flow task, the local variable holding the parameter value is set to 0 and is properly mapped. I did the exact same thing for TABLE_HDR earlier in the same package that ran successfully and returned all of the rows. It's a pretty basic "give-me-everything-since-the-last-time-I-asked" technique that I've used throughout the ETL and it works everywhere except for this one instance.

    When I run the exact same package in a parallel test environment it works fine. The only difference between the two environments is that the source that isn't working has a few thousand more rows.

    I'm stumped. I don't know what else to look for. If I had to guess, I'd say that somehow the parameter value has been reset to NULL but I don't really understand how that could happen.

    Creating a stored procedure isn't an option because I can't write to the source system and the network guys won't let me create a linked server.

    "Beliefs" get in the way of learning.

  • Are you passing the parameter as an integer (e.g. LONG)?

  • I'm passing a local variable as the parameter and the variable datatype is Int64. The source datatype is a NUMERIC (8,0) but, in practice, the value will never exceed 99991231. As I said, virtually all of my data flow tasks use this technique and all of them work except for this one and it works in my development and test environment.

    "Beliefs" get in the way of learning.

  • Right, The local variable type is Int64. However, what is the parameter type (in the SQL task Parameter Mapping tab.) IN your case, that should be LONG, I believe.

  • From what I can see, that's only true for parameters passed to stored procedures. For T-SQL queries the parameters take on the native data type of the SSIS variable. There isn't any way to assign a data type. I have changed the data type of the variable to Int32. Additionally, I have changed the stored proc that assigned the value to the variable explicitly CASTing the variable to an INT. Still doesn't work. However, if I hard code a zero in the query instead of using the parameter it works just fine.

    This would seem to indicate that the value of the parameter is taking a hit somewhere. I'm going to try and capture the query in profiler and see what value appears. I can't think of anything else to try. Even more discouraging is that even if I prove the parameter is getting overwritten I'm not sure what I can do about it.

    "Beliefs" get in the way of learning.

  • Try using an expression on the source component to construct the SQL statement instead of using a parametrized query.

    Something like this:

    "SELECT ColA, ColB, ColC

    FROM TABLE_DTL

    WHERE LastModifiedNumericDate > " + (DT_STR,10,1252) @[User::MyVariable]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Robert Frasca (4/14/2014)


    From what I can see, that's only true for parameters passed to stored procedures. .

    Actually its not so much that but the difference between an Execute SQL Task in the Control Flow and and OLE DB transformation in the Data Flow. For some crazy reason, these two look very different, especially in the area of parameter mapping. I wrongly assumed the former.

  • gbritton1 (4/15/2014)


    Robert Frasca (4/14/2014)


    From what I can see, that's only true for parameters passed to stored procedures. .

    Actually its not so much that but the difference between an Execute SQL Task in the Control Flow and and OLE DB transformation in the Data Flow. For some crazy reason, these two look very different, especially in the area of parameter mapping. I wrongly assumed the former.

    An execute SQL task is different from a source because the execute SQL task doesn't care about metadata and resultsets retrieved, but for the source this is very important.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • An execute SQL task is different from a source because the execute SQL task doesn't care about metadata and resultsets retrieved, but for the source this is very important.

    That's true but is not related to how parameters are set up. The two paradigms are far too different in this regard (e.g. positional vs named). One piece of documentation that could really help for the Execute SQL task is a table mapping SSIS variable types to the SQL data types in the ESQL task parameter mapping tab. Actually I'd like to see a three-way mapping:

    SSIS variable type SSIS Parameter type SQL Data type

    decimal CURRENCY money

    ...

    Some are obvious, some less so...

  • The bottom line is that I can only get it to work on this particular machine if I embed the query in a stored procedure that I must store in the source database. This is not a viable option as I am not allowed to create stored procedures in a third-party product database.

    Frankly, this whole thing is nonsensical. It works perfectly fine in multiple environments against a slightly smaller (i.e. the test database contains 99% of the same data in the production database) subset of the data but refuses to work in the production environment. It doesn't even give me an error, it just refuses to return any rows in SSIS but will return rows if the query is run independently.

    I know I'm preaching to the choir but I really don't have time for this random crap. :crazy:

    "Beliefs" get in the way of learning.

  • I feel your pain! Time for a stupid question: What db is the 3rd-party running? Reason I ask is what I read here:

    http://technet.microsoft.com/en-us/library/dd239343.aspx

    Support for features such as parameters and credentials are dependent on the data provider that you select.

  • Well, now that you mention it, the source database is SQL Server 2005 while the target is SQL Server 2012. In my test environment, both db's are in SQL Server 2012. I'm not sure why that would matter on a relatively simple select statement but, you never know.

    "Beliefs" get in the way of learning.

Viewing 12 posts - 1 through 11 (of 11 total)

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