Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Weird problem with data flow task


Weird problem with data flow task

Author
Message
Robert Frasca
Robert Frasca
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 741
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.
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
Are you passing the parameter as an integer (e.g. LONG)?
Robert Frasca
Robert Frasca
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 741
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.
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
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.
Robert Frasca
Robert Frasca
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 741
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840


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...
Robert Frasca
Robert Frasca
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 741
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search