Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS 2008 - Stored Procedure is asking for parameters, but it is a output parameter

Posted on 2014-04-29
19
Medium Priority
?
1,159 Views
Last Modified: 2016-02-10
Hi,

I am very new to all this..

I have created a Execute SQL Task, and I am trying to pass variable/parameters.

I have this SQLStatement:
SELECT DISTINCT
      FILE_ID
  FROM DATABASE_TABLES
  Where 
      TBL_STAT = 'purge_elig' And 
      TBL_Typ = 'Claim' And
      TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM' and TBL_STAT = 'ACTIVE')
Go
SELECT DISTINCT
      FILE_ID
  FROM DATABASE_TABLES
  Where 
      TBL_STAT = 'purge_elig' And 
      TBL_Typ = 'CLAIM_ITEM' And
      TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM_ITEM' and TBL_STAT = 'ACTIVE')
Go
SELECT DISTINCT
      FILE_ID
  FROM DATABASE_TABLES
  Where 
      TBL_STAT = 'purge_elig' And 
      TBL_Typ = 'CLAIM_FACILITY_DETAIL' And
      TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM_FACILITY_DETAIL' and TBL_STAT = 'ACTIVE')

Open in new window




I want to pass one set of parameters to the "Create and Log Monthly Table" Execute SQL Task

I am first passing FILE_IDS to the "Foreach Loop Container" FILE_IDS is a object.

Then from the "Foreach Loop Container" to the "Create and Log Monthly Table" I pass from FILE_IDS to FILE_ID

The SP that is being passed to has:
@FILE_ID INT,
@FILE_DT date,
@RETENTION_DAYS INT,

I think above is all correct...

See Attachment

Now look on the last page of the snapshot and notice that:
@CLM_TBL_NM is a output parameter

Yet I am getting this error:
SSIS package "ClaimPurge.dtsx" starting.
Error: 0xC002F210 at Create and Log Monthly Table, Execute SQL Task: Executing the query "Exec CreateAndLogMonthlyTable ?,?,?
" failed with the following error: "Procedure or function 'CreateAndLogMonthlyTable' expects parameter '@CLM_TBL_NM', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Create and Log Monthly Table
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at ClaimPurge: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ClaimPurge.dtsx" finished: Failure.
The program '[5516] ClaimPurge.dtsx: DTS' has exited with code 0 (0x0).

Open in new window


after trying to execute the package.

What am I doing wrong?

Please help and thanks
0
Comment
Question by:Amour22015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 

Author Comment

by:Amour22015
ID: 40030229
0
 
LVL 35

Expert Comment

by:James0628
ID: 40044019
I may not be able to help, since I've never used SSIS, but it might help if you could post the actual SP code, so that people could see how @CLM_TBL_NM was being used.  Personally, without that, I have no clue what might be going on.

 James
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 2000 total points
ID: 40044366
If your stored procedure has output parameter, you still need to supply the variable that the output value will be put into.

create mysp(@parm1 int, @parm2 int output)
has to be called as

exec mysp @myvalue1, @myvalue2 output
after which variable @myvalue2 will have the output value.

See http://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx for more details and example.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Amour22015
ID: 40044393
This question has been resolved.  But I am going to need help with a new question that I will be posting about Table Views.  This will involve questions about finding out what is within the current Table View and how I can recreate a new Table View.  Thanks, If you want I can post back the new question...
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40044617
Sure, but that probably will be new question, and if this one is resolved, you might want to accept the solution that helped.
0
 

Author Comment

by:Amour22015
ID: 40046967
I've requested that this question be deleted for the following reason:

Not answered please delete
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40046968
it does not look like the author noticed comment a40044366, which does answer the original question why not passing an output parameter to the stored procedure results in error.
0
 

Author Closing Comment

by:Amour22015
ID: 40046989
Great
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40047103
Please explain grade C you awarded. The link "grading" posted by the moderator above has details about grading.
0
 

Author Comment

by:Amour22015
ID: 40047159
I am sorry after reviewing your answer

I noticed I failed to supply
TBLNMS with a datatype VARCHAR

You are correct, you should get an "A+"

Maybe I can make it up.

I currently have another question that I am finding out that it cannot be done in SSIS but only in SQL Server - Stored Procedure.

"SSIS 2008 R2 - Passing parameter to a Table View"
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40047244
re grade: from the same article:

"If you feel that an inappropriate grade has been awarded, you may use the Request Attention feature to contact the site Moderators, who are able to change the grade awarded at their discretion."

re. your new quesition - it appears that you have deleted it.
0
 

Author Comment

by:Amour22015
ID: 40047285
Yes, the new question needs to be rewritten so that it might be more clear.  But first I don't know if I am clear.

Will change grade

Thanks
0
 

Author Comment

by:Amour22015
ID: 40047297
I don't see the request attention? on the same article? maybe it does not show after closing?
0
 

Author Comment

by:Amour22015
ID: 40047469
I did post a new one:

"SQL Server 2008 R2 - Creating a random dynamic Table View"

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40049213
I don't see the request attention? on the same article? maybe it does not show after closing?
Surely you can see the link at the top that reads:
A Request For Attention has been filed.
0
 

Author Comment

by:Amour22015
ID: 40050208
Could you please change grade from a "C" to a "A" on this post, Thanks
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

618 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