Solved

SSIS 2008 - Passing a parameter/Variable Part2

Posted on 2014-04-24
10
2,232 Views
Last Modified: 2016-02-10
Hi,

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 the FILE_ID
FILE_ID has a datatype set to Int
I am using Execute SQL Task
ResultSet: Full ResultSet
It is a OLE DB Connection Type
I have it in a Foreach Loop Container: Foreach Loop Container of type ADO Enumerator, created a user variable (FILEID) of type Object and set the ADO object source variable attribute of the Foreach Loop to this variable. Went to the variable mappings and set the (User::File_ID, 0) as (Variable, Index).



But something seems to be missing?


When I execute the Task I get a error:
Error: 0xC00291E2 at Get File Id From Database_Table, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
Task failed: Get File Id From Database_Table

When I execute the Foreach Container I get a error:
Error: 0x3 at Foreach Loop Container: Variable "User::FILEID" does not contain a valid data object
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.
SSIS package "ClaimPurge.dtsx" finished: Failure.

Please help and thanks
0
Comment
Question by:Amour22015
  • 5
  • 5
10 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40020288
Can you please check the following settings of your 'Execute SQL Task' (right+click on the component and select "Edit..."):
1. On the General  tab, the ResultSet should be set to "Full result set"
2. On the result set tab, the 'Result Name' should be set to "0" and 'Variable Name' should be User::FILEID.
0
 

Author Comment

by:Amour22015
ID: 40020316
Ok I did that,

When I execute the container I get error:
Error: 0x3 at Foreach Loop Container: Variable "User::FILEID" does not contain a valid data object
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.
SSIS package "ClaimPurge.dtsx" finished: Failure.

When I execute just the SQL Task (that is within the container) I get error:
SSIS package "ClaimPurge.dtsx" starting.
Error: 0xC002F210 at Get File Id From Database_Table, Execute SQL Task: Executing the query "SELECT DISTINCT
      FILE_ID
  FROM DATABASE_TABLES
  ..." failed with the following error: "The type of the value being assigned to variable "User::FILEID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Get File Id From Database_Table
SSIS package "ClaimPurge.dtsx" finished: Success.
The program '[7212] ClaimPurge.dtsx: DTS' has exited with code 0 (0x0).

Please note:
FILE_ID has a datatype set to Int, within the table: DATABASE_TABLES

Thanks
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40020379
Having read your comments again I noticed the problem. The variable of type Object that you use for the result set of the Execute SQL Task should be the same as the Foreach Loop "ADO Object Source Variable". In order to improve readability and remove any grounds for confusion for anyone who happens to come across this package I would use a plural name for the object e.g. User::FileIds (so anyone can see at the first glance that it's a result set not a scalar variable).
So make sure the result set of the Execute SQL Task and the ADO Obj Source Variable of the loop are both set to User::FileIds.

You also need a scalar variable (e.g. User::FileId) of whatever type that suits the FileIds returned by the previous select query, which should be used in the "Variable Mappings" tab of the Foreach Loop container, i.e. Variable = User::FielID and Index = 0.

That should resolve the issue.
0
 

Author Comment

by:Amour22015
ID: 40020455
Ok,

Looks like same error, I am executing the Container:
SSIS package "ClaimPurge.dtsx" starting.
Error: 0x3 at Foreach Loop Container: Variable "User::FILEIDS" does not contain a valid data object
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.
SSIS package "ClaimPurge.dtsx" finished: Failure.
The program '[11532] ClaimPurge.dtsx: DTS' has exited with code 0 (0x0).


But the SQL Task ran with:
SSIS package "ClaimPurge.dtsx" finished: Success.

I have created a new Variable named: FILEIDS


I don't understand:
You also need a scalar variable (e.g. User::FileId) of whatever type that suits the FileIds returned by the previous select query, which should be used in the "Variable Mappings" tab of the Foreach Loop container, i.e. Variable = User::FielID and Index = 0.

are you saying that I need another variable for the query that is mentioned above?
and to name it FILEID and to set the datatype = to the datatype of the Table field which in this case is FILE_ID with a datatype = Int?, If so then what would be the correct datatype within SSIS, I have Int32?

Thanks
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40020473
You need one variable of type object for the complete resultset which will be used for the actual lopping, and also one scalar variable for each column in that result set (in your case one column only) so when inside the loop, it will be used by the package for accessing the current values of the pertinent field.

The error message suggests there's a problem with the Execute SQL Task, i.e. it hasn't properly set the Object variable. Can you please run that task alone (not the whole package) to see if it succeeds? Also is it possible for you to attach snapshot of the settings of this component.
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.

 

Author Comment

by:Amour22015
ID: 40020643
Ok,

I right click on the Execute SQL Task, and select execute Task - It turns yellow then Green which indicates Success.
SSIS package "ClaimPurge.dtsx" starting.
SSIS package "ClaimPurge.dtsx" finished: Success.
The program '[8544] ClaimPurge.dtsx: DTS' has exited with code 0 (0x0)

Here is my snapshots:
SnapshotPassingVariables.docx
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40020707
Hi again, having seen the snapshots I can see exactly what the problem is, but before providing further suggestions on how to resolve it I want to make sure I have a clear understanding of your intended goal without making any assumptions.

Am I correct that you want to run the select query at the top of this page to get a set og FilIds and for every FileId in the result set call a stored proc (in a loop) and pass the current FileId as an input parameter to that proc?
0
 

Author Comment

by:Amour22015
ID: 40020826
Am I correct that you want to run the select query at the top of this page to get a set og FilIds and for every FileId in the result set call a stored proc (in a loop) and pass the current FileId as an input parameter to that proc?

Yes that is correct

Currently this is done manually, that is why I am using SSIS

Also there is going to be other parameters passed like:

YearMonth
Retentiondays: that is how many days to go back from the current date to process some deletions

But for this post I just want to focus on the File_id's

Thanks
0
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 250 total points
ID: 40021114
OK then all you need to do is to delete the succession line that goes from create view task to the loop container,  take the step that gets the FileIds out of the loop container, connect the CreateView  task to the one that gets the FileIds (that's what I was referring to as Exec SQL task in my previous posts), and add a connection line from the latter to the loop container. So the output of GetFileIds will be consumed by the loop container.
The last step is to add a Execute SQL task component in the loop container inside which you can put the code that runs your final proc, passing into it the User::FileId as a parameter.

As for other parameters such as date and retention period, it sounds like you can use package parameters (vs package variables) that you can set at the package value.

I'm sure you can finish it up from this point onward. Good luck!  : )
0
 

Author Closing Comment

by:Amour22015
ID: 40021335
Great thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

20 Experts available now in Live!

Get 1:1 Help Now