Solved

SSIS 2008 - Parameter Mapping

Posted on 2014-04-22
12
884 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 32
Calculated columns 13 60
Using CTE to insert records into a table 2 26
Access 2010 Query Syntax 5 18
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

932 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

13 Experts available now in Live!

Get 1:1 Help Now