Using CTE with Parameters in OLEDB Data Source in SSDT (or why is SSDT so braindead?)
-
Just some wild guesses!
Make sure your oledb connection connects to the right server, and the right database within the server.
Make sure there are no comments with question marks or any other issues in your sql, the parser you are trying to get along with is probably not an sql parser, its more likely a question mark parser 😉
Make sure that the ssdt process has the permissions needed.
Also, test making the entire query fit on one line and see if that makes a difference.
Another edit:
Try the simplest query possible with a parameter (especially of the type you want to use), from what I've read oledb's should be able to use these but it pays to make sure! Once you see whether or not the ? works as a parameter where you want to use it, then you can either build up the query to what you need or if the ? doesn't work at all, at least you'll know! Oftentimes it helps to make a dummy project just to test your assumptions using trivial constructs.Yet another edit:
https://stackoverflow.com/questions/28907316/multiple-parameters-in-a-query-using-a-ole-db-command-in-ssis/28920335
Probably makes one or more of my previous guesses obsolete, but theres some good threads out there about this, apparently more work goes on in the oledb thing than I thought! Make sure you're using the correct version of your oledb drivers also so that every piece of programming knows about the other's capabilities.Also one of them suggested using declared variables in the oledb query, and then in the "set" statements, using the ?'s in them (its in the linked article). All different things to try!
like I said, wild guesses LOL
-
I created a somewhat similar 'fail query' to yours, but which should run on others' systems. Here it is:
WITH SomeData
AS
(
SELECT *
FROM
(
SELECT
*
, rn = ROW_NUMBER() OVER (PARTITION BY c.object_id
ORDER BY c.name
)
FROM sys.columns c
) src
WHERE src.rn = 1
)
SELECT *
FROM SomeData
WHERE SomeData.column_id > ?;Then I created a new package, added an Int32 variable and set its value to 1. Then I configured my data flow source. Everything works OK. What have I done wrong (or right)?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Scott, would you mind giving the following a try: Make the parameter a text data type, and make sure it has a valid default value. See if that helps the parser do what it needs. You could also try to force single quotes around the "?" and see if that makes a difference.
- Phil Parkin - Monday, March 19, 2018 12:17 PMI created a somewhat similar 'fail query' to yours, but which should run on others' systems. Here it is:
WITH SomeData
AS
(
SELECT *
FROM
(
SELECT
*
, rn = ROW_NUMBER() OVER (PARTITION BY c.object_id
ORDER BY c.name
)
FROM sys.columns c
) src
WHERE src.rn = 1
)
SELECT *
FROM SomeData
WHERE SomeData.column_id > ?;Then I created a new package, added an Int32 variable and set its value to 1. Then I configured my data flow source. Everything works OK. What have I done wrong (or right)?
I noticed this snippet from the OP's post:
"Could be due to the type of your parameters, if they are not text/string types."
The parameters are DATETIME2, read from a query against a parameters table. So the parameters (variables) are stored as System.Object.
I wonder maybe if the parameters should be typed better than "system.object"? Phil, can you try those with your query? Just wondering, I'm just trying to play along with the experts here 😉
-
OK, created version 2 of the query, using a DateTime2 column from the standard MDS database:
WITH SomeData
AS
(
SELECT *
FROM
(
SELECT
*
, rn = ROW_NUMBER() OVER (PARTITION BY tsram.Role_ID
ORDER BY tsram.Privilege_ID
)
FROM mdm.tblSecurityRoleAccessMember tsram
) src
WHERE src.rn = 1
)
SELECT *
FROM SomeData sd
WHERE sd.EnterDTM > ?;Added a string parameter with a value of 19000101 and clicked on Preview: no errors.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin - Monday, March 19, 2018 12:48 PM
I am not clear what the OP is having a problem with...it may be related to the source system. If it is something other than SQL Server - then how the query is parsed would definitely make a difference.
I am not able to recreate this - I can set a parameter inside a CTE or outside - and it works.
If the query is that complex, I would declare the parameters up front and use the variables in the code:
Declare @var1 int = ?;
Declare @var2 int = ?;WITH cte
AS (
... complex code
)
, secondCTE
AS (
... more complex code
)
SELECT ... FROM secondCTENow - if the source system is not SQL Server then how the OLEDB driver actually parses the query and whether or not it actually works will all be dependent on the source system. For example, querying a Vertica system and trying to declare variables will not work because they don't have a declare statement or the ability to utilize variables.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Ok, more data points...
My source is SQL Server. I'm copying data from ServerSrc to ServerTgt. I have no rights on ServerSrc other than query data.
My parameters are stored in a parameters table on ServerTgt. Let's call them DateTimeStart and DateTimeEnd. They are DATETIME2(0) format. They are created by a stored process, which (usually) copies the previous DateTimeEnd to the new DateTimeStart, and the new DateTimeEnd is midnight of today. I say usually because the SP allows a manual override in case of recovery situations or initial load. (Not really germane to the problem...probably TMI)
I copy the parameters to SSDT variables via an SQL task and Single Row Resultset.
I'd previously Googled that DATETIME2 SQL Server columns had to be copied to System.Object SSDT variables. I can't find that link(s) right now or I'd reference them.
However, changing the SSDT variable data type to DATETIME seems to work OK. I'm sure I tried that when I first created my packages a couple months ago, and I can't remember what error message SSDT was spewing at the time.
Anyway, after further testing today, based on the information provided in last night's posts (i.e. "it works for me..."), I think I've encountered the crux of the problem. Apologies I didn't find it earlier...
My Source server is Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
My Target server is Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
I've copied a small table from the Source to Target server. It is identical to the source table, just on the target server. The only difference is the schema in which it was saved.
This query from the source server fails:
SELECT *
FROM (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier
ORDER BY replica_valid_from_date DESC)
FROM dbo.FACILITY
WHERE ? <= replica_valid_from_date AND replica_valid_from_date < ?
) AS src
WHERE ROW=1If the only change I make is to change the connection manager to the target server, and add the schema to the query, it works:
SELECT *
FROM (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier
ORDER BY replica_valid_from_date DESC)
FROM tmp.FACILITY
WHERE ? <= replica_valid_from_date AND replica_valid_from_date < ?
) AS src
WHERE ROW=1So, it seems to be server version related. If any of you have SQL Server 2008 lying around I'd love to hear if you encounter the same issue.
BTW, I still think SSDT is braindead with respect to parameters:
http://sqlblog.com/blogs/jamie_thomson/archive/2011/03/14/have-ssis-differing-type-systems-ever-caused-you-problems.aspx
http://blog.hoegaerden.be/2011/06/11/passing-a-datetime-parameter-into-the-execute-sql-task/Edit:
This query also fails in 2008 and works in 2012. Nice trick to know, so thanks for the SO link.
DECLARE @DateTimeStart DATETIME2(0)=?;
DECLARE @DateTimeEnd DATETIME2(0)=?;SELECT *
FROM (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier
ORDER BY replica_valid_from_date DESC)
FROM FACILITY
WHERE @DateTimeStart <= replica_valid_from_date AND replica_valid_from_date < @DateTimeEnd
) AS src
WHERE ROW=1
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply