asked on

SSIS 2008 - Parameter Mapping

Hi and Thanks,

I have this Select statement where it gets the value that I need [FILE_ID]:

SELECT DISTINCT
      FILE_ID
FROM DATABASE_TABLES
Where
      TBL_STAT = 'purge_elig' And
      TBL_Typ = 'Claim'

I would like to use [FILE_ID] as a parameter to pass to other Stored Procedures...

How do I do this???

I can see the:
Parameter Mapping
but I am not sure on how to use it...
I quess it would be a output parameter at this point???
Variable Name: Add: FILE_ID - but it says: A variable value must be specified for the selected variable type, I selected Int32 (because it is a Int type).
Direction: Output
Date Type: ???
Parameter Name: NewParameterName???
Parameter Size: -1???

Please correct when it is wrong???

Thanks

Jim Horn

Dude (Dudette), way too many ??? question marks.

Using your T-SQL, below is an example of a Stored Procedure with two parameters, such that those parameters are used in the WHERE clause

CREATE PROC your_proc (@tbl_stat varchar(25), @tbl_typ varchar(10)) AS

SELECT DISTINCT FILE_ID
FROM DATABASE_TABLES
Where 
      TBL_STAT = @tbl_stat And 
      TBL_Typ = @tbl_typ
GO

Open in new window

To call that proc from anywhere, passing parameters, execute it like this, using any values you wish for the parameters:

exec your_proc 'purge_elig', 'Claim'

Open in new window

Now, how exactly are you calling this SP? 'Parameter Size' implies SSIS.

Jim Horn

I see SSIS was in the question title.

What is the Data Source that you are trying to call the SP from: OLE DB, ODBC, something else? The answer will mean minor differences in the way the SP is called.

Amour22015

ASKER

That would be OLE DB

Thanks

Jim Horn

The screen shots in this blog look like a pretty decent tutorial.

Summary

You'll need to make sure that you have Variables in your package, with the same data type as the parameters that get passed to the SP.

In the SQL Command Text, use question marks for parameters.

Click on the Parameters button

In the Set Query Parameters dialog, the Parameters column = the @parameter_name as defined by the SP, and in the Variables column map the SSIS variables to what gets passed to the SP parameters.

Amour22015

ASKER

In that blog it refers to, after the parameter has been created and outputted?

I am first going and getting the parameter:
SELECT DISTINCT
FILE_ID
FROM DATABASE_TABLES
Where
TBL_STAT = 'purge_elig' And
TBL_Typ = 'Claim'

The parameter that I am looking to pass is: FILE_ID

So would it be something like:?

SELECT DISTINCT @FILE_ID = ?
FILE_ID
FROM DATABASE_TABLES
Where
TBL_STAT = 'purge_elig' And
TBL_Typ = 'Claim'

I am using:
Execute SQL Task within SSIS

thanks

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Now that I did that I have to pass that parameter?

I also have 2 more to be passed.

@FILE_DT is the current date
@RETENTION is 30 days from today

so I need information on how to pass todays date into @FILE_DT?

Something like:
I would take another Exec SQL Task and:

Exec Stored Procedure Name @FILE_ID, @FILE_DT, @RETENTION

Thanks

Amour22015

ASKER

I tried and received error Task went to red:

SSIS package "ClaimPurge.dtsx" starting.
Error: 0xC002F309 at Get File Id From Database_Table, Execute SQL Task: An error occurred while assigning a value to variable "FILE_ID1": "The type of the value being assigned to variable "User::FILE_ID1" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".
Task failed: Get File Id From Database_Table

I know that it is a Int datatype
but when I try to have it match to Int32 it ask for the value
A variable value must be specified for the selected variable type.

please help and thanks

Jim Horn

SQL Server int = 4 byte signed integer = SSIS DT_I4.

Amour22015

ASKER

I do not see a option within the result set for data type DT_I4

Amour22015

ASKER

I ended up doing this:

Execute SQL Task
Within Parameter Mapping:
FILE_ID, DATATYPE = INT32, VALUE = 0

will this work???

How can I tell if the proper fields are being passed within the parameter???

Thanks

Amour22015

ASKER

Great, Thanks