Solved

SSIS 2008 - Passing a Variable/Parameter

Posted on 2014-04-23
8
753 Views
Last Modified: 2016-02-10
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...
0
Comment
Question by:Amour22015
  • 4
  • 3
8 Comments
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 250 total points
ID: 40019813
Hi Amour22015,
First the ResultSet for the 'Execute SQL' component should be set to 'Full ResultSet' because the queries you've typed above will potentially return more than just one row - unless you change the distinct to "Top 1" if that's what you want.  But assuming that you want to pass all returned File_Id's to a 'For Loop' then you should select full result set. Then Choose a Foreach Loop Container of type ADO Enumerator, 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).

You can set a breakpoint for the foreach loop and check the parameter passed to the foreach loop at the beginning of each iteration in debug mode..
0
 

Author Comment

by:Amour22015
ID: 40019864
Ok,

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
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40019889
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.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 40019965
Yes,
moreover, put break point on Execute SQL task and see the variable value in Local Tab. You will see assigned value to variable.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Amour22015
ID: 40019995
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
0
 

Author Comment

by:Amour22015
ID: 40020000
Never mind

This is just a header that you mentioned
(Variable, Index).


Thanks
0
 

Author Closing Comment

by:Amour22015
ID: 40020073
Great thanks
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40020085
Glad that you solved your problem.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

12 Experts available now in Live!

Get 1:1 Help Now