Solved

SSIS 2008 - Passing a parameter/Variable Part2

Posted on 2014-04-24
10
2,398 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

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
 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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