How to pass a custom parameter to Data Manager package

[Former Member]

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.

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.

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

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)

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.

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

Kind Regards

Daniel