August 18, 2010 at 4:09 pm
Hi
I have inherited an ETL from someone with far better SQL skills than I and I need to add a parameter to an existing query. I have done this on numerous others without issue but it would seem that due to the structure of the query SSIS won't recognise the inclusion of the parameter so won't let me alter the code.
Basically the error is parameters cannot be extracted when I add WHERE Datemodified > ? to the end of the below SQL and click the parameters button.
Some hard coding in here, don't be concerned this is a legacy thing..
Any help / advice appreciated, thankyou
WITH
TaskSubType (TaskTypeID, TaskSubTypeID, TaskSubTypeName)
AS
(
SELECT CASE TaskListName_ID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE NULL
END,
TaskListInt,
TaskListShortDesc
FROM [dbo].[TaskListValue] TV
WHERE TaskListName_ID IN (1, 2, 3, 4)
),
TaskPriority (TaskTypeID, TaskPriorityID, TaskPriorityName)
AS
(
SELECT CASE TaskListName_ID
WHEN 11 THEN 1
WHEN 14 THEN 2
WHEN 15 THEN 3
WHEN 16 THEN 4
ELSE NULL
END,
TaskListInt,
TaskListShortDesc
FROM [dbo].[TaskListValue] TV
WHERE TaskListName_ID IN (11, 14, 15, 16)
),
TaskStatus (TaskTypeID, TaskStatusID, TaskStatusName)
AS
(
SELECT CASE TaskListName_ID
WHEN 5 THEN 1
WHEN 6 THEN 2
WHEN 7 THEN 3
WHEN 8 THEN 4
ELSE NULL
END,
TaskListInt,
TaskListShortDesc
FROM [dbo].[TaskListValue] TV
WHERE TaskListName_ID IN (5, 6, 7, 8)
),
TaskReason (TaskTypeID, TaskReasonID, TaskReasonName)
AS
(
SELECT CASE TaskListName_ID
WHEN 12 THEN 2
WHEN 10 THEN 3
WHEN 13 THEN 4
ELSE NULL
END,
TaskListInt,
TaskListShortDesc
FROM [dbo].[TaskListValue] TV
WHERE TaskListName_ID IN (10, 12, 13)
)
SELECT T.[ID] AS TaskHeaderID,
T.[TaskName] AS Name,
T.[Account_ID] AS CustomerID,
T.[TaskType_ID] AS TaskTypeKey,
TT.[TaskTypeName] AS TaskTypeValue,
T.[TaskSubType_ID] AS TaskSubTypeKey,
TS.[TaskSubTypeName] AS TaskSubTypeValue,
T.StartDate,
T.EndDate,
T.DueDate,
T.CompletedDate AS CompleteDate,
T.AssignedToRole_ID AS AssignToRole,
T.AssignedToRep_ID AS AssignTo,
T.CompletedByRep_ID AS CompleteBy,
T.CreatedByRep_ID AS CreatedBy,
T.[PriorityID] AS PriorityKey,
TP.[TaskPriorityName] AS PriorityValue,
T.[TaskStatus_ID] AS TaskStatusKey,
TU.[TaskStatusName] AS TaskStatusValue,
T.[TaskReason_ID] AS StatusReasonKey,
TR.[TaskReasonName] AS StatusReasonValue,
T.[Category_ID] AS ProductCategoryID,
PC.[CategoryName] AS ProductCategoryName,
T.[Brand_ID] AS ProductBrandID,
PB.[BrandName] AS ProductBrandName,
T.[TargetType] AS TargetTypeKey,
V.[ListShortDesc] AS TargetTypeValue,
T.[Target] AS TargetValue,
T.[Score] AS ScoreValue,
substring(T.[TaskDesc], 1, 2000) AS Description,
T.[TaskNote] AS TaskNote,
isnull(T.[IsActive], 0) AS IsActive
FROM [dbo].[Task] T
INNER JOIN [dbo].[TaskType] TT ON T.[TaskType_ID] = TT.[ID]
INNER JOIN [TaskSubType] TS ON T.[TaskType_ID] = TS.[TaskTypeID] AND T.[TaskSubType_ID] = TS.[TaskSubTypeID]
INNER JOIN [TaskStatus] TU ON T.[TaskType_ID] = TU.[TaskTypeID] AND T.[TaskStatus_ID] = TU.[TaskStatusID]
LEFT JOIN [TaskPriority] TP ON T.[TaskType_ID] = TP.[TaskTypeID] AND T.[PriorityID] = TP.[TaskPriorityID]
LEFT JOIN [TaskReason] TR ON T.[TaskType_ID] = TR.[TaskTypeID] AND T.[TaskReason_ID] = TR.[TaskReasonID]
LEFT JOIN [dbo].[ProductCategory] PC ON T.[Category_ID] = PC.[ID]
LEFT JOIN [dbo].[ProductBrand] PB ON T.[Brand_ID] = PB.[ID]
LEFT JOIN [dbo].[ListValue] V ON V.[ListName_ID] = 67 AND T.[TargetType] = V.[ListLookupID];
August 18, 2010 at 6:52 pm
Without seeing how you're adding that clause to the "end" of the statement, I can't say for certain - but I will say that you can't place parameters inside a subselect - SSIS (or the OLEDB/ADO provider) just can't decipher them.
Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components
August 18, 2010 at 10:19 pm
Thanks for the reply Todd.
I was simply putting the where clause before the final ; in the listing above.
This was the only sub select in all the packages so I suspected it may be the case, next thing is how I rewrite it....
August 19, 2010 at 6:12 pm
As an update to this if anyone else comes across a similar issue. I managed to get the With statement running along with the parameter by using SQL command from variable. The main WITH SQL in one parameter and referencing the variable in that.
Works a treat, thanks for your help, hope this may be of use.
January 24, 2011 at 10:39 pm
hello,
i came across the same problem. I have an OLEDBSource for DB@ with a simple select statement in it. however when I try to click the parameters button, i'm getting this error:
TITLE: Microsoft Visual Studio
------------------------------
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.
------------------------------
ADDITIONAL INFORMATION:
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft DB2 OLE DB Provider)
------------------------------
BUTTONS:
OK
------------------------------
Does anyone know how to fix this?
thanks!
January 25, 2011 at 6:23 am
Are you sure your OLE DB provider supports parameters?
As the error said, maybe you should construct your dynamic SQL statement in a variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 25, 2011 at 8:38 am
I have seen this problem many times in SSIS and .Net, it really comes down to the provider not being able to pick out where to replace the parameters in the code. This isn't some mickey mouse provider but the MS provided providers (maybe Mickey Mouse by your definition 🙂 ). For any kind of compound code in an EXEC SQL task it is nearly impossible to use parameters. The workaround is to build the command out and then execute the command in the variable, it isn't pretty but it works.
CEWII
July 8, 2013 at 12:03 pm
Hi, Is it still the same workaround for getting through this problem?
July 8, 2013 at 2:02 pm
Could you not turn this into a stored-procedure and pass in the parameters using SSIS's "?" flag for parameters? I believe the syntax is EXECUTE dbo.MyProc ?, ?, ? (as an example)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 8, 2013 at 2:19 pm
I have had to resort to using a sql statement from a variable, then set the variable earlier in the package.
I have created a table variable that contains the parameter (same basic idea as proc).
Then I set the sql variable to be "SELECT * FROM dbo.itfTableVariable(" + DT_STR(30,1252) @Variable + ")". Or something similar using proper ssis syntax.
I think I have had this problem with sql data sources. Execute sql tasks work fine, but the data source sql has to be very plain.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply