Execute SQL task parameters not in the WHERE clause
-
Hi,
Wondering if anyone can tell me where I'm going wrong. Basically, I want to pass some parameters into a SQL statement but not only in the WHERE clause e.g.
SELECT
(yearval*DATEDIFF(dd,?,?))/365
WHERE
<date column> BETWEEN ? AND ?
I need to do something like this to ensure that the year value is reduced according to the time period I'm looking at. So, the first and third parameter and second and fourth would be the same.
I've tried setting this up using the parameter page to define the four input parameters but SSIS complains with the following
"Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I haven't had any problems when I've placed parameters in the WHERE clause so I'm wondering whether the problem is that you're not allowed to pass parameters into the SELECT clause.
Does anyone know if this is the case or can tell me where I'm going wrong? If there isn't a fix then I guess I'm going to have to call it as a stored procedure.
Thanks,
Ian
-
Hi Ian
have you tried creating 2 DateTime variables 'StartDate' and 'EndDate'
then using them as 4 Input Date parameters in the Parameter Mapping section of the Execute SQL Task Editor :
User::StartDate Input Date @StartDateP1
User::EndDate Input Date @EndDateP2
User::StartDate Input Date @StartDateP3
User::EndDate Input Date @EndDateP4
and then use them in order in your query
SELECT
(yearval*DATEDIFF(dd,?,?))/365
WHERE
<date column> BETWEEN ? AND ?
this should work.
Hoda
-
Hoda,
That is what I did, except that the parameters were strings. Replacing the parameters with the strings directly in Management Studio worked fine so it's quite happy with the conversion from varchar to datetime in the DATADIFF function and it works fine when I pass the four strings into a stored procedure with exactly the same format of SQL statement so it looks to me like it's SSIS that isn't binding the parameters in the SELECT clause correctly.
Ian
-
This is the known issue that parametter can not be passed in any inner query or inside function call like DATEADD in your case.
I have faced same problem when passing parametter in WHERE clause of inner query and derived table and it does not work at all. while it does perfectly in outer queries.
hope this help.
Note: I have already reported this bug to microsoft few days ago hope this will get fixed in SP2
FurrukH baig
-
I think you might be right, that this is a SSIS issue of not binding it correctly, or maybe i might not be setting something correctly, not sure yet. Even though passing the params at the given positions (within DATEADD and the where clause) works perfectly fine inside query analyzer. I did expect it to work in a stored procedure as you say it did, with the params as string or date. However, I will try to see if i can come up with something to make it work directly in a select query, hopefully.
Hoda
-
Hello,
I am getting the same error message when I try to run an Execute SQL task containing the following:
SELECT * FROM dbo.ft_GetParentTableInfo (?,?)
Does this mean that we cannot use functions at all from SSIS? Or is there a way roound it?
I would welcome any suggestions!
thanks
-
I don't know if the above use of parameters is a bug but the way I solved the problem is to save the values that go into the parameters into a temp table and then read them from the table join. Something like:
INSERT INTO tempTable(par1,par2,par3,par4) VALUES(?,?,?,?)
SELECT
...
FROM yourTable as t1 (join) tempTable as t2 on t1.someid=t2.someid
WHERE
t1.date column BETWEEN t2.par3 AND t2.par4
Look at this post from yesterday:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=309359
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply