June 7, 2011 at 12:34 pm
I have created a User variable (varMaxDate) that simply holds the maximum date from a SQL table. I have an Execute SQL Task created that pulls that data from the DB and assigns it to that varMaxDate variable. What I would like to do is use that varMaxDate variable within the WHERE clause of another SQL query. Within the second Execute SQL Task I added the following expression for the SQLStatementSource property: "select * from Person.Contact WHERE (ModifiedDate >= " + (DT_WSTR,50) @[User::varMaxDate]
My first task runs fine and I see the value changing in the varMaxDate variable when debugging. However, the second task fails and throws this error:
[Execute SQL Task] Error:
Executing the query "select * from Person.Contact WHERE (ModifiedDate >..."
failed with the following error: "Incorrect syntax near '4'.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
What is the proper way to accomplish this simple task?
June 7, 2011 at 12:41 pm
In the next EXEC SQL task you simply use the placeholder ? and then use the parameter mapping to say that that variable IS the ?
CEWII
June 7, 2011 at 4:21 pm
So here is what I have now:
SQL Statement from Execute SQL Task 2:
select * from Person.Contact WHERE ModifiedDate >= ?
Parameter Mapping from SQL Task 2:
Variable Name: User::varMaxDate
Direction: Input
Data Type: DBTIMESTAMP
Parameter Name: ?
Paramter Size: -1
Throwing this error now:
[Execute SQL Task] Error: Executing the query "select * from Person.Contact WHERE ModifiedDate >=..." failed
with the following error: "Invalid time format". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not established correctly.
My varMaxDate variable is of type DateTime. When I change the parameter mapping in the second Execute SQL Task mapping I get these associated errors:
DataType:
DBTIME
[Execute SQL Task] Error: Executing the query "select * from Person.Contact WHERE ModifiedDate >=..."
failed with the following error: "Invalid time format". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
DBDATE
[Execute SQL Task] Error: Executing the query "select * from Person.Contact WHERE ModifiedDate >=..."
failed with the following error: "Unsupported data type on parameter binding 0.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection
not established correctly.
DBDATE2
[Execute SQL Task] Error: Executing the query "select * from Person.Contact WHERE ModifiedDate >=..."
failed with the following error: "Unsupported data type on parameter binding 0.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection
not established correctly.
June 8, 2011 at 2:28 pm
Can anyone help me out with the data type issue that I explained earlier?
June 8, 2011 at 4:50 pm
porcrim (6/8/2011)
Can anyone help me out with the data type issue that I explained earlier?
what's the data type for your variable: varMaxDate
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
June 8, 2011 at 10:16 pm
varMaxDate is of type DateTime. I have tried setting the parameter mapping to the following data types:
Date
DBDate
DBTime
DBTimeStamp
June 9, 2011 at 7:24 am
porcrim (6/8/2011)
varMaxDate is of type DateTime. I have tried setting the parameter mapping to the following data types:Date
DBDate
DBTime
DBTimeStamp
Try setting up the second Execute SQL Task #2 as follows...
General
SQL Source Type: Direct
SQL Statement: select * from Person.Contact WHERE ModifiedDate >= ?
Parameter Mapping
Variable Name: User::varMaxDate
Direction: Input
Data Type: Date
Parameter Name: 0 (zero, not the letter)
Also, you may want to confirm the scope of your variable (User::varMaxDate) and use breakpoints to monitor the value.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
June 9, 2011 at 9:57 am
That worked. I had a second Execute SQL task in there configured that way but it wasn't working until I deleted that task and recreated it. Thanks for your help.
June 9, 2011 at 10:49 am
porcrim (6/9/2011)
That worked. I had a second Execute SQL task in there configured that way but it wasn't working until I deleted that task and recreated it. Thanks for your help.
awesome - glad you got it working.
i too have been in situations were simply deleting the component, adding it back, and configuring from scratch has appeared to be the solution.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply