Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1037
  • Last Modified:

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
0
Amour22015
Asked:
Amour22015
  • 7
  • 5
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Amour22015Author Commented:
That would be OLE DB

Thanks
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Amour22015Author Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hang on ... the above was thinking that you wanted to call the SP, and use parameters in the WHERE clause.  Different deal.

To run the SP, and insert the FILE_ID into a variable
Still an Execute SQL Task
General - ResultSet = Single Row (aka your FILE_ID value)
General - SQL Statement - Enter your SQL statement here, with NO references to parameters or variables.
ResultSet - Hit The Add button, type the name of the column in the ResultName column, and the variable you want to insert it into in the Variable Name column.
0
 
Amour22015Author Commented:
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
0
 
Amour22015Author Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SQL Server int = 4 byte signed integer = SSIS DT_I4.
0
 
Amour22015Author Commented:
I do not see a option within the result set for data type DT_I4
0
 
Amour22015Author Commented:
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
0
 
Amour22015Author Commented:
Great, Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now