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:
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:
after trying to execute the package.
What am I doing wrong?
Please help and thanks
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')
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).
after trying to execute the package.
What am I doing wrong?
Please help and thanks
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
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
I've requested that this question be deleted for the following reason:
Not answered please delete
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.
ASKER
Great
Please explain grade C you awarded. The link "grading" posted by the moderator above has details about grading.
ASKER
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"
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.
"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.
ASKER
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
Will change grade
Thanks
ASKER
I don't see the request attention? on the same article? maybe it does not show after closing?
ASKER
I did post a new one:
"SQL Server 2008 R2 - Creating a random dynamic Table View"
Thanks
"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.
Surely you can see the link at the top that reads:
A Request For Attention has been filed.
ASKER
Could you please change grade from a "C" to a "A" on this post, Thanks
ASKER
Snap-Shot-with-Execute-inside-Co.docx