June 7, 2012 at 6:04 am
Hi
I have used below three parameters in SSIS.
Year
Code
Batch
Year is Mandatory.
EXEC [dbo].[Sample_SP] ?,?,?
If i executed the package by giving input to all the three parameters, it worked fine.
The thing is that if i gave input to year and left others blank, it didnt give me the output.
Please suggest how to use optional parameter in SSIS
Regards
SqlStud
June 7, 2012 at 6:13 am
You'll need to find a way of assigning NULL to the variable that you pass in. If you can't do that, change the stored procedure so that it treats empty strings as NULLs, and assign an empty string as the variable value.
John
June 7, 2012 at 6:16 am
You will have to adjust your SP to accept an empty string ('') for those parameters and handle that. See Gail's blog post here to get the main idea. The "simple yet not always good performance" way is to use IF statements and separate queries. This worked for me at my old employer because the larget table only had about 300,000 rows and most had under 100,000.
Sample:
CREATE PROC test
@year int, @code int, @batch int
AS
BEGIN
IF (@code IS NULL OR @code = '') AND (@batch IS NULL OR @batch = '')
BEGIN
SELECT columns
FROM table
WHERE year = @year
END
...
...
...
END
Of course, with this method you have to account for all combinations (11 assuming they can leave all blank), so going Gail's route may be a better choice for you.
Jared
CE - Microsoft
June 7, 2012 at 6:48 am
Great Jared and Thanks.
I have used the below query. Is it Correct?
YEAR = @MLR_YR
AND (@CODE IS NULL OR CODE='' OR CODE=@CODE)
AND (@BATCH IS NULL OR @BATCH ='' OR BATCH= @BATCH)
Regards
SqlStud
June 7, 2012 at 7:30 am
sqlstud (6/7/2012)
Great Jared and Thanks.I have used the below query. Is it Correct?
YEAR = @MLR_YR
AND (@CODE IS NULL OR CODE='' OR CODE=@CODE)
AND (@BATCH IS NULL OR @BATCH ='' OR BATCH= @BATCH)
Regards
SqlStud
This is not a functioning query, so I'm not sure what you are doing here. Please post the entire proc.
Jared
CE - Microsoft
June 14, 2012 at 1:33 am
Thanks Jared. its working now.
Another issue is that how can i pass optional parameter while running the package through SQL Server Agent job.
Regards
SqlStud
June 14, 2012 at 5:15 am
Again, you're not giving me enough information. Please post the entire proc and describe in detail what you want to do with it.
Jared
CE - Microsoft
June 14, 2012 at 5:27 am
Hi
-----------------------------------
CREATE PROC test
@year int, @code int, @batch int
AS
BEGIN
IF (@code IS NULL OR @code = '') AND (@batch IS NULL OR @batch = '')
BEGIN
SELECT columns
FROM table
WHERE year = @year
END
...
...
...
END
--------------------------
As per your above suggestion, i have modified my code to pass the optional parameter in SP and package(sample.dtsx) is working fine.
Next thing is that, i have created SQL Server Agent Job to run the package(sample.dtsx).
My concern is how to set the optional parameter in the SQL Server Agent job properties ?
Regards
SqlStud
June 14, 2012 at 6:02 am
I'm not sure I understand... If it is a job, how will you optionally pass the parameter? Based on a condition?
Jared
CE - Microsoft
June 18, 2012 at 1:02 am
Jared,
As per your suggestion, i have modified SP(handled mandatory and optional parameters) and everything is working fine.
Is it any possible way to set/pass optional parameters in Sql Server Agent Job? No condition is defined.
Regards
SqlStud
June 18, 2012 at 5:52 am
Again, I don't think I understand what you are trying to do. A job runs on a schedule, so how would it know what parameter is used unless it is defined? Your SP can take optional parameters, but the job will be scripted something like "EXEC usp_spname @parameter = 1" and will alwyas run with that parameter unless you change it.
Jared
CE - Microsoft
June 18, 2012 at 6:30 am
Trying to execute the SSIS package thru Sql server agent job... My concern is whether we have to set the parameters value in the Job step properties.
Please find the attachment
Regards
SqlStud
June 18, 2012 at 7:31 am
Ok, I see now. Take a look at this thread and see if it helps. I personally have never had to do this.
Jared
CE - Microsoft
June 19, 2012 at 5:21 am
SQLKnowItAll (6/18/2012)
Ok, I see now. Take a look at this thread and see if it helps. I personally have never had to do this.
Thanks Jared
Regards
SqlStud
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply