Solved

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

Posted on 2014-04-29
19
1,078 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
19 Comments
 

Author Comment

by:Amour22015
ID: 40030229
0
 
LVL 34

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 500 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

746 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

11 Experts available now in Live!

Get 1:1 Help Now