Solved

SSIS 2008 - Parameter Mapping

Posted on 2014-04-22
12
879 Views
Last Modified: 2016-02-10
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
Comment
Question by:Amour22015
  • 7
  • 5
12 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40014871
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40014890
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
 

Author Comment

by:Amour22015
ID: 40014917
That would be OLE DB

Thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40014922
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
 

Author Comment

by:Amour22015
ID: 40015738
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40015756
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Amour22015
ID: 40015811
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
 

Author Comment

by:Amour22015
ID: 40015863
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40015896
SQL Server int = 4 byte signed integer = SSIS DT_I4.
0
 

Author Comment

by:Amour22015
ID: 40015927
I do not see a option within the result set for data type DT_I4
0
 

Author Comment

by:Amour22015
ID: 40017302
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
 

Author Closing Comment

by:Amour22015
ID: 40018471
Great, Thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now