cancel
Showing results for 
Search instead for 
Did you mean: 

Passing multiple parameter to SSIS package via data manager

Former Member
0 Kudos


Hi

We are trying to pass parameters from data manager to SSIS package. We are trying to give option of selecting members from Dimension using SelectInput while modifying script.

But we are not able to catch the values in SSIS package.

Code in Data manager package:

(ON)

(SELECTINPUT,%VER%,,"Select Account",%ACCOUNT_DIM%)

(Execute sp_ver_copy,SqlStatementSource,exec dbo.sp_ver_copy2 '%VER%')


We also tried with writing GLOBAL(VER,%VER%) instead of -- (Execute sp_ver_copy,SqlStatementSource,exec dbo.sp_ver_copy2 '%VER%')

But that also dosent work.

We need to use the parameters passed, in SQL code that we have written in OLE DB source, under data flow task.

Any help is appreciated.

Thanks in advance

Accepted Solutions (0)

Answers (2)

Answers (2)

GFV
Active Contributor
0 Kudos

Dear Rajat,

generally i do use a syntax like the following, when using propmts based on Dimension:


PROMPT(SELECTINPUT,%TIME_VL%, , "Chose YTD Period","%TIME_DIM%")

GLOBAL(TIME_ID,%TIME_SET%)

Be aware that:

- name of global variables (in the DTSX) and prompt variables (in the DM Script) should not be the same

- if you use <dimension>_SET variable (eg TIME_SET) will report values separated by ","

Also with stored procedure we do use a similar syntax:


PROMPT(RADIOBUTTON,%INPUT_1%,"First Var",ONE,{"One","Two"},{"ONE","TWO"})

PROMPT(RADIOBUTTON,%INPUT_2%,"Second Var",ALPHA,{"Alpha","Beta"},{"ALPHA","BETA"})

GLOBAL(INPUT1,%INPUT_1%)

GLOBAL(INPUT2,%INPUT_2%)

Hope it helps

GFV

former_member186498
Active Contributor
0 Kudos

Hi Rajat,

can you please share all your package from bpc?

Thanks

     Roberto

Former Member
0 Kudos

Thanks Roberto for reply.

Please find the screenprint as per your suggestion. Also I have mentioned the code in above question. We are using the stored procedure to write the value into a table , passed from front end. We are able to write the value passed from front end into a table. So it means that system is passing the value.

But when I try to bring same value into the data task flow , system is throwing a error. The description of error is also mentioned below.

Kindly advise.

Error Description in DM package:

Description = Executing the query "exec dbo.sp_ver_copy2 'Account'" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_STR)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.