Another ForEach Imitation

May 23, 2008



We have a posting for News & Announcements. There are multiple announcement types and multiple regions. I'm attempting to create an sProc that will allow me to get the most recent announcement inserted into a table for every region of each announcetype. I was original just grabbing the TOP 10* with ORDER BY DESC, but now they want one announcement for each announcetype for every region. The Statement needs to be dynamic since the number of announcetype's will change and the number of regions may change as well.

All I have now, after changing things around 100 times, is:


AS



BEGIN

DECLARE @ONE TABLE(

AnnounceType VARCHAR (100)

, RegionName VARCHAR (20)

, Title VARCHAR (500)

, URL VARCHAR (250)

)

INSERT INTO @ONE

SELECT DISTINCT AT.AnnounceType, MAX (REG.RegionName) RegionName, HJA.Title, HJA.URL

FROM dbo.HotJobAnnouncements HJA

JOIN dbo.AnnounceType AT

ON HJA.AnnounceTypeID = at.AnnounceTypeID

JOIN CORE.dbo.Regions REG

ON HJA.Region = REG.Region

GROUP BY AT.AnnounceType, HJA.Title, HJA.URL

SELECT * FROM @ONE

ORDER BY AnnounceType, RegionName, Title

END

If anyone can help me or point me in the right direction I will appreciate it.

View 1 Replies


ADVERTISEMENT

Foreach Loop Container Foreach File Enumerator Sort Files

Apr 4, 2007



How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.



Dave

View 27 Replies View Related

Foreach Loop Container - Foreach ADO Enumerator How Performance Compares To Use Of Cursors In Stored Procs

Apr 22, 2008



Hello

I have a question

How is foreach loop container - foreach ADO enumerator performace in SSIS package compares to use of cursors in stored procedures

Is there any articles comparing them

I understand a lot of factors can affect the performance, however what is expected performance for the foreach ADO enumerator loop for large dataset. What is Microsoft recommendation for that - recommended - not recommended (using large datasets - over million records)

Thank you
Arminr Bell

View 4 Replies View Related

Foreach File Enumerator - Is Absent In Foreach Loop Component

Apr 11, 2007

Hi,

I have no "Foreach File Enumerator" oprtion in the Enumerator Property of the Foreach Loop Component.

I have this enumerator in the c:Program FilesMicrosoft SQL Server90DTSForEachEnumerators folder.

Also I check it in the GAC - it does not here. I try to execute gacutil.exe -iF ForEachFileEnumerator.dll, but it is failed with "Failure adding assembly to the cache: The module was expected to contain an assembly manifest." Seems it is not managed enumerator.



Please help me.

Also information on how to regeister unmanaged enumerators are welcome!

View 3 Replies View Related

Missing Foreach File Enumerator And Foreach Item Enumerator

Aug 4, 2006

Ever since installing SQL Server 2005 SP1, when using the Foreach Loop
container in SSIS the Foreach File Enumerator and Foreach Item Enumerator are
missing from the Enumerator drop down box. Anyone else seen this issue? and
know how to fix it?


thanks



Eric

View 17 Replies View Related

Foreach?

Nov 9, 2006

Hi,

I€™m not sure whether a €œForeach€? capability is achievable in SQL; I€™ve examined joins, unions and subqueries but can€™t come up with a solution.

Scenario is 3 related tables: Location, Room, Booking (date and guest name) and what I wish to do is produce a data grid that shows every for each Location and for each room the status on a given day as follows:

Location Room Date Guest
Main 1 1/1/06 Jones
Main 2 1/1/06 Smith
Main 3 null null
Annex 1 1/1/06 Bloggs
House 1 null null


In psuedo-code what I want to do is

For a given date
Show each location
for each location show each room
for each room show guest (if present) else show null.

I can generate Location and Room OK using LEFT OUTER JOIN but when I introduce the date check it all goes pear shaped in that (obviously) the grid is not populated with nulls in place of the non-existent booking records!

View 7 Replies View Related

Hi, ForEach In TPL

Mar 18, 2008



HI Friends,


This is Naga new comer in our site...

please send some examples of ForEach in TPL...This is Urgent for me....


Thanks & Regards,
Naga

View 1 Replies View Related

ForEach

Feb 5, 2008

Hi I have created a ForEach loop based on a custom collection of 1 column containing string values (a list of table names).
I want to loop thru this and save the row value (Table Name) into a user variable that will then be used to lookup the table name from within my data task, which is inside the loop.

anyone know how to do this?

Thanks
Shailen S.

-------------

Found my solution.

The Variable mappings section allows you to define a variable that will hold the contents of the iterator. This will be a user defined variable.

View 3 Replies View Related

Need Help With Foreach ....Anyone...........

Nov 6, 2006

Hello,

I am new to SSIS. I need to create a SSIS package that can loop through the databases and create a flat file of each table in the database specified.

Is it possible with Foreach to loop through databases and tables? How should I proceed with this?

I would probably need step by step procedure because I am new.

I would really appreciate your help.

Thank you.





View 4 Replies View Related

Help With ForEach ADO Enumerator

Mar 5, 2007

Hi:

Would like to do a file System task and a data flow for each row in a table.

I use a ForEach container with ADO enumerator set to a rowset variable. The rowset contains all rows from a single table. What I want to do is as in pseudo-code below:

for each row in ADO rowset


varDestination=CurrentRow.col_file_name.value

FileSystemTask.Destination=VarDestination

FilesystemTask.Copy(Source to Destination)(copy excel file from fixed folder to multiple variable folders)

DataFlowtask.Destination=varDestination(Write data to an excel destination
Next
My problem is that I dont know how to access the values of fileds from my current row in the rowset. Request help and pointers to samples.
TIA
Kar

View 5 Replies View Related

ForEach From Query

Apr 16, 2006

Hi All

I'm sure this is a simple thing to do, but I'm new to SSIS and trying to catch up fast.

I want to execute a query on the database which will give me a path and a filespec, say:

c:apps estapp1

and

fred*.csv

No problems here.

I then want to feed them into a ForEach loop and interate through all the files matching the filespec at that location. I can't figure this out at all.

Thanks for you help in advance.

FG

View 4 Replies View Related

Accessing Row In ForEach

Dec 16, 2007

Hello everybody.
I'm having a problem:
As the result of Dataflow task I get a recordset with about 50 columns in it. Now I need to loop through each row in the dataset and make some manipulations with the data in all 50 columns in a single script task. So I need 'em as separate variables.
Ofcourse I can make a For Each Loop container with ADO enumerator, and map each column to specific variable. But that's not a very good thing, because if the format changes, i will have to remap everything again (takes a lot of time to remap 50 values)

So is it possible:
1. To map using not index of columns, but there names?
2. To pass actual row into the script task?

View 7 Replies View Related

Backwards In A Foreach For ADO?

Aug 28, 2007

I need to simulate cursor-type (groan) behavior in a dataset and am wondering if this is possible in the foreach task. Example - The user has the need to go through the data row-by-row, and if a certain value is missing in row 10 then go back to row 8, grab a value from that row, and plug it in the missing column in row 10. Then move on to row 11.

Is there a way to make the foreach ADO enumerator travel backwards? Is there a way to do this that will not be awfully inefficient? Any suggestions out there would be welcome.

I've advocated for set-based updates, and these simply aren't an option, as each successive row depends on the updates that may have happened above it in the sequence. Unless I hear of any other ideas out there I have to move forward with this sequential type operation.

View 4 Replies View Related

Foreach Loop?

Jul 10, 2006

I need to execute about dozen packages from another package... how do I dynamically pass the dozen package names to the package and execute using foreach loop...?

idea is to store the names of packages in a text file and set the file connection property reading each package names from the text file... in this way I can just configure/edit the text file from time to time, the packages and the units that I want to execute...

Someone please provide me steps to make it work.

Thanks in adv.

View 4 Replies View Related

Foreach File

Aug 3, 2007



I loop through a list of files in a directory and one of the tasks in the container is to validate the xml file against dtd if this succeeds i process the file and the lforeachloop gets the next file. If validation fails i then delete that file,the problem i am having is that whe the xml task fails it stops the whole for each container even though the failparentonfailure is set to false.

How can i get the foreach container to continue to the next file after the xmltask fails (validation) and the file is succesfully deleted?


PS is their away of posting the DTSx screenshot as this might explain it better?

View 6 Replies View Related

Foreach ADO Enumerator?

Dec 18, 2006

my setting in foreach component like below shown

Enumerator: Foreach ADO Enumerator

ADO Object source variable: user::strsql

strsql like "select distinct name from table"

Enumeration mode: Rows in the first table

my goal: transform data according to the distinct every name

however execute error

prompt: Variable "User::strsql" does not contain a valid data object

pls help,thanks in advance

View 1 Replies View Related

SQL ForEach Select

Apr 7, 2008

I have two tables, [Property] and [Booking]. What I need to do is select every ID in [Property], then move to [Booking] and select every booking which matches [Property].[ID]. I've been trying for hours but I just can't get it to work. This is what I have so far:



Code SnippetSELECT Property.[Property ID], Booking.[Visit Commencement Date]
FROM Property, Booking
WHERE Property.[Property ID] =
(SELECT DISTINCT Property.[Property ID] FROM Property);





But I can't work out how to select every row. I'm using MS Access 2007 to run the query.

View 6 Replies View Related

ForEach Restrictions

Dec 5, 2007

I'm trying to upload various excel files stored in a folder scanning their sheets.
The package has a double loop of foreach, one inside the other: the external pickup the file names, the internal browse the sheets.
Everything is working fine but I need to skip one of the sheets that contains only directions for the users.
I've tryed to use the restrictions in the foreach but from what I found you can only define the sheet names to use and not the names to be avoided. Does exist a sintax to set name!="directions$" ?

As alternative solution I should use a script component (inside the foreach) that check the variable contents and, if the sheet name is ok, pass the control to the data flow task. Other suggestions?

Thanks
j.c

View 5 Replies View Related

For/Foreach Loop

Jun 10, 2007

Hi,



I'm looping through some query data and doing a Script Task check inside the loop on a datetime field.



If the datetime happens before 10 o'clock:

- Store the data row in Table1

If the datetime happens after 10 o'clock:

- Store the data row in Table2



How can I store the datarow inside the loop without inserting it into a database table?

I need to access the data in the next step after the loop. How can I do this?



Thank you very much!



View 4 Replies View Related

Need Help With Foreach SMO Enumerator Or....Anyone.......Please....

Nov 7, 2006



Hello,

I am new to SSIS. I need to create a SSIS package that can loop through the databases and create a flat file of each table.



Some one suggested me SMO Enumerator in Foreach Loop. But how can I create flat files of each table in AdventureWorks Database.



Is it possible with Foreach to loop through databases and tables?

I would probably need step by step procedure because I am new.

I would really appreciate your help.

Thank you.

View 5 Replies View Related

SQL Update / FOREACH Query

Apr 4, 2007

Hi Folks,Could someone please enlighten me on the syntax I should be using to create a for-each structure within a Stored Procedure? Here's what I'm trying to do:Run through a "project" table, extracting the columns [id int], [name varchar] of each row, inserting these into the "reporting_table"Then, for each [project id] now in reporting_table, I need to calculate the value of the [total_cost numeric(18,2)] field in order to update it in the reporting_table:Run though the [orders] table (a master table), WHEN [project id] = [project id of current iteration], @totalCost += [orderTotalCost]Insert @totalCost into reporting_table's [total_cost] column.I.E. basically totalling the total cost of all orders in the order table by Project I hope that makes sense? Maybe I'm over complicating it somehow? The reason I'm using a reporting_table is that .NET's built in GridView would be perfect for displaying this type of data, and it's very easy to bind a GridView object directly to a table. Thanks in advance,Ally 

View 4 Replies View Related

SP Not Firing In Foreach Loop

Feb 28, 2008

The foreach loop below runs fine and it sends emails as expected but the SP does not update the Companies table.  Any thoughts?  How do I cause the SP to execute?  
 PROCEDURE newdawn.EmailSentDate      @CID intAS     UPDATE Companies      SET Companies.LastEmailDate = (GetDate())     WHERE tblCompanies.C_ID = @CID RETURN
foreach (GridViewRow row in GridView3.Rows)        {            pstrTo = row.Cells[2].Text;            CEmail = row.Cells[2].Text;            CName = row.Cells[3].Text;            CID = row.Cells[1].Text;            CState = TextBox1.Text;            CCity = row.Cells[5].Text;            CCat = row.Cells[4].Text;            CCID = row.Cells[0].Text;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);            SqlCommand cmd = new SqlCommand("EmailSentDate", con);            cmd.CommandType = CommandType.StoredProcedure;            cmd.Parameters.AddWithValue("@CID", CCID);                                    try            {                System.String mailServerName = "mail.domain.com";
                               REST of code sends email to email address found in each row ....it all works find but SP above does not fire.
 

View 12 Replies View Related

SqlDataSource Foreach Query

Jan 30, 2006

I'm using a sql data source and I need to perform a foreach query. I have a collection of id's and I need to hit my database (the one available via sql data source) for each of the id's in the collection. I know that the sql data source returns a data view and it can be coverted to a data table. What's the best way to query a database using the sql data source ojbect for each id in a collection? Here's my code.
<code>
private void GetJobsFromPSI(JobQueueCollection jobs)
{
// Create a data view.
DataView dv = new DataView();
// Get the id's.
JobQueueCollection jobs = GetJobsFromQueue(Membership.GetUser().ProviderUserKey());
// Now that we have all of the user id's we
// need to call out the jobs from PSI.
foreach (JobQueue job in jobs)
{
// Get query for each id in the job collection.
// Somehow add a new datarowview to the data view for each
// query.
}
}
</code>

View 1 Replies View Related

Foreach Loop Container

Jan 22, 2008

Hello Experts,

Sorry that I open a new thread again but I didn€™t found any good tutorials about foreach loop container. I would like to read every row from one table and do something.
I red something about sql task€¦ Can someone give a little instruction, please?

Kind regards,
Alex

View 3 Replies View Related

Recordset Destination Used In A FOREACH?

Feb 1, 2007

Hi all,

Can a Recordset destination be used as source for a ForEach loop.

Correct me if i'm wrong but the Recordset is stored in a variable of type Object? So what stops my ForEach loop from itterating?

Regards,

Pieter

View 3 Replies View Related

Using Foreach Loop Container

Feb 15, 2007

Hi



I using data flow task to import from flat file to database but i need to use Foreach Loop Container to import multiple files in specific folder and all will be insert in the same table



tahnks & Regards



View 1 Replies View Related

Doing An Insert In A ForEach Loop

Feb 27, 2008


Help with Looping in a SSIS Package
Scenario:
We have a web app that lets our existing clients insert new locations into a table (Clients) in a SQL Server DB. This table has an Identity column as the PK. This table also has another ID field HBID that is the PK for another table (HLocation) in another Database system (Sybase). The HBID field is given a Default value of €˜1€™ during an insert operation in our (SQL Server) table, Clients. The Sybase database uses a Sequence table for inserting a PK into the table. Whenever our clients insert a new record in Clients (SQL Server), I need to generate a new HBID from the Sequence table in (Sybase), update the HBID in the Clients table (SQL Server) and then finally insert the record with the new HBID into table HLocation (Sybase).
I have devised a SSIS package for this as following: Note all variables are scoped at the Package Level.
Execute SQL Task #1 that drives a For Each Loop with the following properties:
General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
SELECT COID, HBID, Name, DateCreated
FROM Client
WHERE HBID = '1'

ResultSet: = Full Resultset

Result Set:
Result Name = 0
VariableName = USER::rsClient Variable Type = Object

ForEachLoop with the follwing properties:

Collection:
Foreach ADO Enumerator
ADO Object Source Variable = USER::rsClient
Enumeration Mode = Rows in first table
Variable Mappings:
Variable: Index:
COID 0
HBID 1
Name 2
DateCreated 3

Inside of the ForEachLoop I have another Execute SQL Task to generate a new HBID from Sybase set up as following.

Execute SQL Task #2

Connection Type: OLE DB
Connection: Sybase
SQL SourceType: Direct Input
SQLStatement:
UPDATE Autoinc SET INC_LAST = (INC_LAST+1) WHERE INC_KEY ='HBID';
SELECT INC_LAST AS NewHBID FROM Autoinc WHERE INC_KEY ='HBID'

ResultSet = SingleRow

Result Set:
Result Name = NewHBID
VariableName = USER::NewHBID, VariableType = Int32

Also Inside the ForEachLoop is a Script Task that has all of my variables as ReadWrite = COID, HBID, Name, DateCreated and NewHBID. I concatenate the values in a string a pass the string into a Message Box to make sure they are looping correctly and they are.For example the results might look like the following:

12698, 1, John Doe Trucking, 10/1/2007, 14550
13974, 1, Joe Smith Trucking, 10/1/2007, 14551
10542, 1, Dave Jones Trucking, 10/1/2007, 14552
Etc.

The values 14550 -14552 are the new HBID being generated in the loop.

The problem is that when I try to Update the HBID in the Client table (SQL Server) with another Execute SQL Task I keep getting the same NewHBID number.
In this case 14550 would be updated for every record instead of the next number in the loop.

I have set up Execute SQL Task #3 as follows:

General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
UPDATE Client
SET HBID = ?
WHERE HBID = '1'(SELECT COID, HBID, Name, DateCreated FROM Client)

ResultSet: = Full ResultSet

Result Set:
Result Name = 0
VariableName = USER::rsNewClient, VariableType = Object

Parameter Mapping:
VariableName USER::NewHBID, Direction = INPUT, DataType = Long
ParameterName = 0

I€™ve tried putting Execute SQL Task #3 inside of the ForEachLoop, connecting it to the output of the ForEachLoop. I€™ve tried setting up a dataflow with a Derived Column using the USER::NewHBID as the Expression.

I still get the same results, 14550 added to every row.

Can any one help or shed some light?

Any and all suggestion will be deeply appreciated!

Thanks !!!

View 1 Replies View Related

Foreach Loop - Value Back To 0

Jun 14, 2007

Hi,



Let's say you have a Data Flow Task that connects to a Foreach Loop, looping through the data.



Somewhere inside the Foreach Loop, you set an int variable MyInt to a value.



My question:

Is it possable when each iteration begins to set MyInt to 0?



Thanks in advance.

View 4 Replies View Related

Foreach Endless Loop

Feb 28, 2007

I'm having a problem where I'm using a Execute SQL Task to retrieve a dataset and storing that in an object variable. Then on success of that execute sql task I use a foreach loop task to go through the dataset and do 2 tasks inside the foreach loop. When I execute this package I have ~12 records in the dataset however when I get to the foreach loop in the 2nd iteration it keeps repeating it. It acts like it is stuck on the second record (tuple) and never goes on. I'm using an ForEach ADO Enumerator in the foreach. I've even set a breakpoint on each iteration and no task fails in side the foreach loop. I'm completely perplexed why it will iterate to the 2nd record but get stuck there in an endless loop. I've tried this on 2 different computers (with different data values) and the same thing happens. Does anyone have any suggestions?

View 40 Replies View Related

Issues About Foreach Loop

Oct 10, 2006

I defined Foreach File Enumerator,there are more than 2 files needed to deal with,it donesn't work if i don't specify filename in flat file connection manager editor,however if i specify filename then doesn't foreach files

what should i do? thanks

View 4 Replies View Related

ForEach Loop Editor

Oct 9, 2006

Help,

Trying to through a process together and using the "ForEach Loop" task. When configuring the task and using the ForEach Loop Editor I do not have the "ForEach File Enumerator" in the drop down. Anyone know whats going on or what I need to do to make this appear. I only see 5 selections: ADO, ADO.Net Schema Rowset, Variable, NodeList, and SMO Enumerators. I am also using the SQL 2005 eval with SP1.

Thanks,

John

View 2 Replies View Related

Foreach Loop Issue

Jan 3, 2007

Here is what I am attempting to get accomplished. I have an SSIS package that contains a Foreach loop container. This container executes a number of SQL tasks in order: SQL Task 1, SQL Task 2, SQL Task 3.

if the SQL task 1 succeeds it should flow on to SQL Task 2 and 3. This works fine when the SQL tasks do not fail...

In the event of any SQL Task failing control should flow to a send mail task to alert about the failure. Next the Foreach loop container should go to the next enumeration in the Foreach loop container and start the next new SQL task 1. So far I have been able to get the control to flow to the send mail task when a SQL Task fails. What does not work is when one SQL Task fails the entire Foreach loop fails and does not move to the next enumeration. It should only fail the package and move on.

Any help would be appreciated....

View 5 Replies View Related

Foreach Variable Cannot Be Applied

Apr 30, 2007

I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.



The SQL Task that is linked to the Foreach container is somthing like this:

SELECT str1, str2, str2, nValue1, nValue2

FROM MyTable



In the SQL Task I've set the following

ResultSet = Full Result Set

Connection Type = OLE DB

Result Name = 0



I've created the following Variables with a Package scope



s1 as String

s2 as String

s3 as String

n1 as Double

n2 as Double



When I run the package I get the following errors

Foreach variable mapping number 4 to variable "User : : n1" cannot be applied

Foreach variable mapping number 5 to variable "User : : n2" cannot be applied



The data type of nValue1 and nValue2 in MyTable are both numeric(19,4)



In setting the variable types in the package it seems that the closest to numeric(19,4) would be double. Is this why it is failing? Is there any way around it? I just need to pass the variables which are numeric and have decimal places.



Any insight would be greatly appreciated.



Regards,

Bill

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved