Solved

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

Posted on 2014-04-29
19
1,091 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

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
SYbase 4 31
Oracle Query - Return results based on minimum value 8 32
Need help how to find where my error is in UFD 6 29
SQL server vNext 18 27
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

773 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