Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

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

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
Avatar of Amour22015
Amour22015

ASKER

Avatar of James0628
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
ASKER CERTIFIED SOLUTION
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
Sure, but that probably will be new question, and if this one is resolved, you might want to accept the solution that helped.
I've requested that this question be deleted for the following reason:

Not answered please delete
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.
Great
Please explain grade C you awarded. The link "grading" posted by the moderator above has details about grading.
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"
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.
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
I don't see the request attention? on the same article? maybe it does not show after closing?
I did post a new one:

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

Thanks
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.
Could you please change grade from a "C" to a "A" on this post, Thanks