SQL Task - passing parameters

Hello,
I have a SQL task wich executes the following statement on a OLEDB connection (SQL 2005 DB):

TRUNCATE TABLE DimTime
GO

DECLARE @CurrentDate AS Datetime
DECLARE @EndDate AS Datetime

SET @CurrentDate = '10-20-2003';
SET @EndDate = '10-20-2005';

while(@CurrentDate < @EndDate)
begin
INSERT INTO DimTime SELECT
DATEPART(month, @CurrentDate) AS MonthNumberOfYear
,DATEPART(quarter, @CurrentDate) AS CalendarQuarter
,DATEPART(year, @CurrentDate) AS CalendarYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester
,DATEPART(quarter, @CurrentDate) AS FiscalQuarter
,DATEPART(year, @CurrentDate) AS FiscalYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester

set @CurrentDate = dateadd(month, 1, @CurrentDate)
end
GO

Now I want to parametrize the statement creating an external variable:

1. I created a datetime package variable (DimTimeStartDate)

2. Add update the above SQL statement to

SET @CurrentDate = ;

3. I mapped the package variable to a new parameter (name 0)

Executing I get the following error:

[Execute SQL Task] Error: Executing the query " DECLARE @CurrentDate AS Datetime DECLARE @EndDate AS Datetime SET @CurrentDate = ; SET @EndDate = '10-20-2005'; while(@CurrentDate < @EndDate) begin INSERT INTO DimTime SELECT DATEPART(month, @CurrentDate) AS MonthNumberOfYear ,DATEPART(quarter, @CurrentDate) AS CalendarQuarter ,DATEPART(year, @CurrentDate) AS CalendarYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester ,DATEPART(quarter, @CurrentDate) AS FiscalQuarter ,DATEPART(year, @CurrentDate) AS FiscalYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester set @CurrentDate = dateadd(month, 1, @CurrentDate) end " failed with the following error: "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.

Not clear what is wrong. Any help

Thanks,
Pierre

Answer this question

SQL Task - passing parameters

GaryBROE

Hello Jamie,
I set the SqlStatementSource property expression to:

"TRUNCATE TABLE DimTime
GO

DECLARE @CurrentDate AS Datetime
DECLARE @EndDate AS Datetime

SET @CurrentDate = @[User::DimTimeStartDate] ;
SET @EndDate = '12-31-2050';

while(@CurrentDate < @EndDate)
begin
INSERT INTO DimTime SELECT
DATEPART(month, @CurrentDate) AS MonthNumberOfYear
,DATEPART(quarter, @CurrentDate) AS CalendarQuarter
,DATEPART(year, @CurrentDate) AS CalendarYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester
,DATEPART(quarter, @CurrentDate) AS FiscalQuarter
,DATEPART(year, @CurrentDate) AS FiscalYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester

set @CurrentDate = dateadd(month, 1, @CurrentDate)
end
GO"

Executing the task I still get: "[Execute SQL Task] Error: Executing the query " DECLARE @CurrentDate AS Datetime DECLARE @EndDate AS Datetime SET @CurrentDate = @[User::DimTimeStartDate] ; SET @EndDate = '12-31-2050'; while(@CurrentDate < @EndDate) begin INSERT INTO DimTime SELECT DATEPART(month, @CurrentDate) AS MonthNumberOfYear ,DATEPART(quarter, @CurrentDate) AS CalendarQuarter ,DATEPART(year, @CurrentDate) AS CalendarYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester ,DATEPART(quarter, @CurrentDate) AS FiscalQuarter ,DATEPART(year, @CurrentDate) AS FiscalYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester set @CurrentDate = dateadd(month, 1, @CurrentDate) end " failed with the following error: "Must declare the scalar variable "@".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

Do I miss something

Thanks,
Pierre
ErwinTsai

yes, but changing the declaration as you are suggesting I get an expression validation error "The variable CurrentDate was not found in the variables collection."

I can declare it as variable but I still don't understand why the first way I used (standard one), even if I set the correct parameters I get an error.

The way you are suggesting is perfect to be resilient to data source changes. Here my only ds is a OLEDB (sql server)

Thanks,
Pierre
David Anton

Great, it works. Thanks.

Pierre
ken1234567

Yeah, don't use parameters. Use a property expression instead.

As explained here: http://blogs.conchango.com/jamiethomson/archive/2005/10/05/2244.aspx

-Jamie
Johan Andersson
Jamie,

I am having the same problem described above and have attempted to use the solution suggested, but can't get it to work. The SQL code is as follows:

DECLARE @ProductCode AS VARCHAR(10);
SET @ProductCode = " + (DT_STR,10,1252)@Product_Code + ";
UPDATE Tele_Archive
SET Reporting_Year =
(
SELECT RY.Reporting_Year
FROM Reporting_Year RY
WHERE RY.Product_Code = @ProductCode
AND RY.Begin_Date <= Tele_Archive.Order_Date
AND RY.End_Date >= Tele_Archive.Order_Date
)

Product_Code in the 2nd line is a user variable of data type string that is global to the package. I have tried various combinations (e.g., using 2 or 3 single quotes in place of the double quotes) to specify the value to be used to set @ProductCode without any success. If I hard code a string value to use to set @ProductCode, they query works as expected. Thank you for any help you can provide. As specified above, I get the following error:

Error: 0xC002F210 at Execute SQL Task - Set Reporting Year for each entry in Archive Table, Execute SQL Task: Executing the query "

DECLARE @ProductCode AS VARCHAR(10);

SET @ProductCode = " + (DT_STR,10,1252)@Product_Code + ";

UPDATE Tele_Archive

SET Reporting_Year =

(

SELECT RY.Reporting_Year

FROM Reporting_Year RY

WHERE RY.Product_Code = @ProductCode

AND RY.Begin_Date <= Tele_Archive.Order_Date

AND RY.End_Date >= Tele_Archive.Order_Date

)

" failed with the following error: "Invalid column name ' + (DT_STR,10,1252)@Product_Code + '.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
alois paulin

You don't need to use expression...

Simply change the property value of BypassPrepare to true in SQL Task..

And separate this query to another SQL Task.

TRUNCATE TABLE DimTime
GO

I've tested your script in my pc, and executed with no error..~^^

ADConsulting / SQLLeader.com / Daeseong Han
jaypatel

Yeah, remember that you're building a string and substituting the values of some variables. So it needs to be like this:

"TRUNCATE TABLE DimTime
GO

DECLARE " + (DT_STR, 20, 1252)@CurrentDate + " AS Datetime
DECLARE " + (DT_STR, 20, 1252)@EndDate + " AS Datetime

...

...

"

Get the idea

-Jamie
smo5024
Thank you. Setting that option in combination with parameter mapping for the user variable solved the problem. Final query was:

DECLARE @ProductCode AS VARCHAR(10);
SET @ProductCode = ;
UPDATE Tele_Archive
SET Reporting_Year =
(
SELECT RY.Reporting_Year
FROM Reporting_Year RY
WHERE RY.Product_Code = @ProductCode
AND RY.Begin_Date <= Tele_Archive.Order_Date
AND RY.End_Date >= Tele_Archive.Order_Date
)

with parameter mapping of:

Variable Name|Direction|Data Type|Parameter

User::Product_Code|Input|VARCHAR|0

and package level user variable for Product_Code defined as a string.

Thanks again!

robepstein

Friedel wrote:
I guess that what Jamie meant was something like this:
Set @CurrentDate = " + (DT_STR, 20, 1252)@DimTimeStartDate + ";
Set @EndDate = '12-31-2050';

and not:
DECLARE " + (DT_STR, 20, 1252)@CurrentDate + " AS Datetime
DECLARE " + (DT_STR, 20, 1252)@EndDate + " AS Datetime

sometimes he thinks too quick ;)
Fridtjof

AHA! Doh.

Sorry guys. Got there in the end though eh!!

-Jamie

G.G.

The way I am suggesting should be considered best practice and should always be used, not just used as a last resort.

In other words, forget about trying to make it work using parameters. You don't need to.

-Jamie
Vestra
I guess that what Jamie meant was something like this:
Set @CurrentDate = " + (DT_STR, 20, 1252)@DimTimeStartDate + ";
Set @EndDate = '12-31-2050';

and not:
DECLARE " + (DT_STR, 20, 1252)@CurrentDate + " AS Datetime
DECLARE " + (DT_STR, 20, 1252)@EndDate + " AS Datetime

sometimes he thinks too quick ;)
Fridtjof
SQL Task - passing parameters

Answer this question