Amour22015
asked on
SSIS 2008 - Passing a Variable/Parameter
Hi,
I would like to know if I am passing a parameter correctly and how you can see what is being passed?
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: Single row
It is a OLE DB Connection Type
I have it in a Foreach Loop Container
So I go to the:
Result Set
Add Variable
Name: FILE_ID
Namespace: User
Value Type: Int32
Value: 0
<Ok>
Is this all correct?
How do I check what is being passed?
Thanks...
I would like to know if I am passing a parameter correctly and how you can see what is being passed?
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: Single row
It is a OLE DB Connection Type
I have it in a Foreach Loop Container
So I go to the:
Result Set
Add Variable
Name: FILE_ID
Namespace: User
Value Type: Int32
Value: 0
<Ok>
Is this all correct?
How do I check what is being passed?
Thanks...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No problem, add a "Foreach Loop Container", then right+click and "Edit..."; in the "Collection" tab set the Enumerator attribute to "Foreach ADO Enumerator" and the "ADO Object Source Variable" to a new variable of type Object that would be the resultset of the previous 'Execute SQL Task' component.
Then go to the "Variable Mappings" tab and follow on as previously explained. Let me know if it's still not clear.
Then go to the "Variable Mappings" tab and follow on as previously explained. Let me know if it's still not clear.
Yes,
moreover, put break point on Execute SQL task and see the variable value in Local Tab. You will see assigned value to variable.
moreover, put break point on Execute SQL task and see the variable value in Local Tab. You will see assigned value to variable.
ASKER
On this:
Go to the variable mappings and set the (User::File_ID, 0) as (Variable, Index).
I went to the variable mappings of the Foreach Loop Container
But I only see:
Variable: I selected the newly created variable
Index: is set to 0
But I don't see:
(Variable, Index).
Also do I not have a parameter/variable within the Execute SQL Task or do I just remove?
Thanks
Go to the variable mappings and set the (User::File_ID, 0) as (Variable, Index).
I went to the variable mappings of the Foreach Loop Container
But I only see:
Variable: I selected the newly created variable
Index: is set to 0
But I don't see:
(Variable, Index).
Also do I not have a parameter/variable within the Execute SQL Task or do I just remove?
Thanks
ASKER
Never mind
This is just a header that you mentioned
(Variable, Index).
Thanks
This is just a header that you mentioned
(Variable, Index).
Thanks
ASKER
Great thanks
Glad that you solved your problem.
ASKER
Sorry I am very new to all this...
I have set the resultSet to 'Full ResultSet' Within the Execute SQL Task
Now I am at the Foreach Loop Editor, but I don't see the option: "Container of type ADO Enumerator" I also looked under properties and did not see that option there either? I currently have a Foreach Loop Container, is that the correct one?
Please explain closer:
create a user variable of type Object and set the ADO object source variable attribute of the Foreach Loop to this variable. Go to the variable mappings and set the (User::File_ID, 0) as (Variable, Index).
Thanks