Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Weird problem with data flow task Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 9:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:57 PM
Points: 545, Visits: 643
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.
Post #1561514
Posted Monday, April 14, 2014 9:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 268, Visits: 599
Are you passing the parameter as an integer (e.g. LONG)?
Post #1561530
Posted Monday, April 14, 2014 9:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:57 PM
Points: 545, Visits: 643
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.
Post #1561543
Posted Monday, April 14, 2014 10:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 268, Visits: 599
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.
Post #1561578
Posted Monday, April 14, 2014 11:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:57 PM
Points: 545, Visits: 643
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.
Post #1561600
Posted Monday, April 14, 2014 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
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]




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1561629
Posted Tuesday, April 15, 2014 12:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 268, Visits: 599
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.
Post #1562018
Posted Tuesday, April 15, 2014 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1562020
Posted Tuesday, April 15, 2014 1:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 268, Visits: 599


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...
Post #1562028
Posted Tuesday, April 15, 2014 1:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:57 PM
Points: 545, Visits: 643
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.


"Beliefs" get in the way of learning.
Post #1562034
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse