How to Pass Optional parameters in SSIS

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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