Skip to Content

I have been reviewing the forums and noticed that there seems to be a few questions on how to pass a parameter from a data manager prompt in BPC to a custom task within a SSIS package. Although there is various methods in which you can do this, I thought I would do a quick blog on how to achieve this with some screen shots.

The scenario is one where we want to prompt the user for a value in a data manager prompt and then using that value we pass it to a custom task within the SSIS package. In this scenario it will be a Execute SQL task calling a SQL Stored Procedure and using the value entered from the user as an input parameter for the store procedure.

I hope to create a similar blog in the NW space if time permits. I hope that this will help out othera who might have this requirement.

Step 1: Create a new SSIS package in the Business Intelligence Environment Studio and save the file to the relevant BPC WebFolders\<Application>\DataManager\Packages\ folder path. The reason why we are saving this file in this directory is because once we add the package from the data manager interface, when we browse for a package that will be the only path accessible path.

Capture.JPG

Step 2: Create a variable called MODIFYSCRIPT with the data type of string. The scope of the variable needs to be package wide, meaning that the scope will be the package name.

 

Capture1.JPG

Step 3: Create your necesary SQL connection strings and then drag a Execute SQL task to the package. Edit the package and select your connection which you just created. In the SQLStatement put in the code to execute the stored procedure.

For the sake of this example: The following SQL Script was entered [exec EPIC.dbo.sp_ver_copy ”], Please take note that the stored procedure is expecting an input variable, and we have to submitted a value. This is because it will get populated from a user entered prompt

Capture2.JPG

Step 4: Save the package in BIDS and the next step is to add the package to BPC. Log into BPC and from the eData menu, click on organize Package List. Once the screen appears, right click on the yellow section of the screen and click on ‘Add Package’. Browse to the path where you saved the SSIS package. (Please Note: This is the relative path to WebFolders\<Application>\DataManager\Packages)

Capture3.JPG

Step 5: Once the package is saved, click on the ‘View Package’ button to edit the dynamic script of the package. once the window is open, click on the advanced button. In the Dynamic Script Editor window. Paste the below code and amend to your requirements.

 

DEBUG(ON)
PROMPT(TEXT,%VER%,"What version would you like to copy ",,"")
TASK(Execute sp_ver_copy,SqlStatementSource,exec EPIC.dbo.sp_ver_copy '%VER%')

The variable %VER% contains the values entered from the data manager prompt

The ‘TASK(Execute sp_ver_copy,SqlStatementSource,exec EPIC.dbo.sp_ver_copy ‘%VER%)’ command contains the Execute sp_ver_copy which is task name in the SSIS package, SqlStatementSource is the property of the task and exec EPIC.dbo.sp_ver_copy ‘%VER%’ is SQL statement which gets generated with the user entered prompt value.

Capture6.JPG

Capture5.JPG

I hope this points you in the right direction and helps others out there with similar requirements.

Kind Regards

Daniel

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Johnny J

    Thanks Daniel. Great Article.

    I have some questions, could you give me some advice,please?

    1.Do we need to setup “parameter mapping”?

    2. Could you share EPIC.dbo.sp_ver_copy code for us?

    3. How about multiple parameters. I know how to setup in BPC, but have no idea to map them to a stored procedure program.

    I will be grateful for any help you can provide.

    Johnny

    (0) 
    1. Daniel Jacinto Post author

      Hi Johnny

      I did not use paramter mapping, I know that you can use paramter mapping in certain instances, but honestly I have had difficulty getting it working.

      If you want to have the stored procedure return an output paramter then use that value in the package then you will need to use paramter mapping. If you are just using input parameters then you can simply the BPC Dynamic Script to input values into the stored procedure.

      In terms of the stored procedure code, it is a simply stored procedure which accepts an input parameter.

      For Example:

      create proc sp_ver_copy

           @inpt varchar(100)

      as

      begin

           insert into EPIC.dbo.text

                (colmnA)

           values(@inpt)

      end

      go

      The BPC Dynamic Script command

      TASK(Execute sp_ver_copy,SqlStatementSource,exec EPIC.dbo.sp_ver_copy ‘%VER%’)  will change the values of the property SqlStatementSource at runtime of the package. And in that way you will be able to submit values to the stored procedure.

      If you want to have multiple parameters, then in your stored procedure you will need to make sure that it accepts multiple parameters.

      I hope this helps

      Kind Regards

      Daniel

      (0) 
    1. Daniel Jacinto Post author

      Thank you Kadir for the feedback,

      in order to pass multiple parameters to the same SSIS task, you would modify your dynamic script accordingly.

      Example:

      TASK(Execute sp_ver_copy,SqlStatementSource,exec EPIC.dbo.sp_ver_copy ‘%VER%’,‘%PARAM1%’,‘%PARAM2%’,‘%PARAM3%’,‘%PARAM4%’,‘%PARAM5%’ )

      Hope this helps

      Kind Regards

      DAniel

      (0) 

Leave a Reply