FOREACH Loop Container: Continue ?

With a ForEach container, configured to loop through files in a directory, if I have a problem with a file.. can I direct the loop to skip on to the next file?
I'm processing structured files, first record of each is some header info, body records are in the middle, and then the last record is a trailer containg a checksum
So, for each file in the directory, I split the records into three raw files, one for header rec(s) , one for body recs and one for trailer recs. (based on line numbers and using a conditional split to direct the records)
Then I start by processing the header recs in a dataflow.. if all goes well there I move on to the next dataflow to process the body recs from the DataRecs raw file.. etc...
I would like to do some validation at each processing step.. if a header rec fails validation say... then I'd like to just stop processing that file and move onto the next file...
Now, I don't see my validation throwing an exception... so its more that I'd decide (maybe using an Audit ) that the header doesn't pass validation.. then I'd like to put a record in an error table (with info about filename, source etc, not just content of the current data row)
But not sure what approach to take on this...
If there is an appropriate section in BOL please point me at it...
Thanks
PJ

OK, so a record could fail validation but won't necassarily cause an error, is that right?

If you can find a way of populating a boolean variable stating whether the validation was successful or not then you could put a conditional precedence constraint beween data-flow1 and data-flow2 to determine whether data-flow2 should execute or not.

-Jamie

|||

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

PJ

|||

Ok... so have to figure out how to access global variables in a script task....

OR...

...could do a conditional split.. which carries out the validation checking...and has one output connected to an OLE DB Destination to store a valid header record.. the other output directed to a RowCount...

If the row count >0 then we have a validation failure.. so could set the global variable there... is there a way to send a row count to a package variable?

PJ

|||

PJFINTRAX wrote:

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

PJ

Absolutely! (Unless you have any other OnSuccess precedence constraints from data-flow1). Once all work is done for that iteration then it'll go onto the next iteration.

|||

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

|||

Jamie Thomson wrote:

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

DOH! Well, we all know I'm only making this up as I go along :^)

Thanks

PJ

|||

sigh.. this is like pulling teeth... now another problem has reared its ugly head...

when i use the SQL Task to retrieve the ID of the FileHeader record (that I just inserted) into a package variable... I get a datatype error.. and can't figure out which bloomin data type to use...(for the variable)

In my DB table the ID is a bigint....

what on earth is the equivalent in SSIS datatypes.... I have literally tried em all.. only one that works is Object !!!

So, SQL Task is executing SQL query as follows "select MAX(ID) as ID from fileheader"

Table definition is :

CREATE TABLE [dbo].[FileHeader](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Source] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileName] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoadDate] [datetime] NULL CONSTRAINT [DF_FileHeader_LoadDate] DEFAULT (getdate())
) ON [PRIMARY]

So what should my variable type be if its to hold the result of the query?

PJ....

|||

Ahh what they hell.. I'll just use String.. that works....

|||Guys, I have the bigint problem again, can't just use a string this time...
has anyone any idea what the SSIS datatype equivalent of a bigint is at all?
Thanks
PJ
|||Is this just so obvious that nobody is bothering to answer?
|||

Probably cos they don't know. And I'm afraid I include myself in that. I don't have a SSIS instance to hand but as and when I do I'll try and take a look OK.

-Jamie

|||

Thanks Jamie,

but I have been through every single datatype .. and if I try to wedge a bigint into anything other than a string I get an error...

I would have thought an INT64 would work.. but no...

the bigint is an identity column in a table in my db... I'm a result set (which includes it) into an object and using that as a recordset using a foreach.. and I need to compare the ID column numerically.. to decide which flow to go down (ie If FiletypeID >12 and FileTypeID > 4 )

i suppose since the presedence contstraint is an expression I could try casting to a int64 maybe...

bit awkward tho.... would have thought this would all be easier... (like most SSIS things, heh)

PJ

For each container - performance problem?

I have a for each container configured to iterate over files in a directory (around 2600 files). The for each container contains only one data flow task. The data flow task is very simple. Only 3 steps. Read from file using flat file source (ragged right), format column values (eg. converting YYYYMMDD into yyyy/mm/dd and TRIM spaces - 13 such conversions) using derived column transform and inserting them into a table (23 columns - mostly nvarchar, few numerics and few dates) using Ole Db Destination. The table has one non-unique clustered index on nvarchar(18) column. Each of the 2600 file has at an average 750000 rows.

It seems to me that memory deallocation doesn't happen after each iteration of the loop (I might be wrong). So after few iterations, sqlservr.exe is hogging all available memory (2GB in this case) whereas my dual processors are running at 3-6 % of capacity. As a result, to process less than 5 million rows, it is taking 6 hours.

I am surprised that for such a trivial operation, performance should degrade this much.

One thing that is suspicious is that the files are residing on a USB 2 external drive.
Also, the database files are on the same drive. But, still... why first few iterations zap through like missiles while the later ones seem to be competing with snails?

Any insight?

TIA,
NiteshDo you use transactions? If yes, I suspect the cause of the problem that all files are submitted as part of single transaction - so the Sql Server has to keep all the data before committing the transactions.

I would rather avoid transactions unless really needed and use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.|||Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie|||

Michael Entin SSIS wrote:

Do you use transactions? ...

No, I am not using Transactions.

Michael Entin SSIS wrote:

...use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh|||

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh|||

Nitesh Ambastha wrote:

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh

There's only one way to find out :)

I'll be brave and say it should be able to handle it. Remember, all it does is swap buffers in and out of memory - once the contents of a buffer is inserted into the target the buffer "disappears".

You may have to play with buffer sizes etc... to get optimal performance but that sounds like an interesting piece of work if you ask me (perhaps that's just me! )

Let us know how it goes anyway. You should definately try it.

-Jamie|||

Nitesh Ambastha wrote:

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh

Correct. SQL Server Destination can only be used when the target is local.

-Jamie