Amour22015
asked on
SSIS 2008 - Passing a parameter/Variable Part2
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_MAXIMUMERRORCOUNTREA
SSIS package "ClaimPurge.dtsx" finished: Failure.
Please help and thanks
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_MAXIMUMERRORCOUNTREA
SSIS package "ClaimPurge.dtsx" finished: Failure.
Please help and thanks
ASKER
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_MAXIMUMERRORCOUNTREA
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
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_MAXIMUMERRORCOUNTREA
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
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.
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.
ASKER
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_MAXIMUMERRORCOUNTREA
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
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_MAXIMUMERRORCOUNTREA
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
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.
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.
ASKER
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
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
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?
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thanks
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.