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

Footer rows to skip

OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

.
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
.
0000260611

It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables.

Dima.

I too have the same type of flat files.

If u have solved this issue. Do help me please.

|||You can use another Data Flow to read the file a second time, with each row as a single column. Use a conditional split to identify the header/footer rows that you're interested in and derived columns (or script if necessary) to do any parsing before sending them to a destination. The downside here is that you have to read the file twice.

Alternatively, you can tackle the whole job in a single Data Flow with a script component that accepts the file rows as a single column, parses them in code, outputs the detail rows in discrete columns, and does whatever necessary for the header and footer. This is slightly more complicated from a code standpoint and the parsing logic could be a lot more tedious than letting the Flat File Source component do it.
|||Or perhaps read the file one time to get the number of rows in a variable and add a rownumber column to each row. Then in a second data flow use a conditional split to redirect the footer rows. This should work if you have a fixed number of rows in the footer.|||Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

|||

killerless wrote:

Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

Yeah. Read the other posts. And you can use a script component in the data flow to assign a value from the footer to a variable to that you can use it in your sanity check.

|||Sorry, Thank you.

Footer rows to skip

OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

.
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
.
0000260611

It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables.

Dima.

I too have the same type of flat files.

If u have solved this issue. Do help me please.

|||You can use another Data Flow to read the file a second time, with each row as a single column. Use a conditional split to identify the header/footer rows that you're interested in and derived columns (or script if necessary) to do any parsing before sending them to a destination. The downside here is that you have to read the file twice.

Alternatively, you can tackle the whole job in a single Data Flow with a script component that accepts the file rows as a single column, parses them in code, outputs the detail rows in discrete columns, and does whatever necessary for the header and footer. This is slightly more complicated from a code standpoint and the parsing logic could be a lot more tedious than letting the Flat File Source component do it.
|||Or perhaps read the file one time to get the number of rows in a variable and add a rownumber column to each row. Then in a second data flow use a conditional split to redirect the footer rows. This should work if you have a fixed number of rows in the footer.|||Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

|||

killerless wrote:

Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

Yeah. Read the other posts. And you can use a script component in the data flow to assign a value from the footer to a variable to that you can use it in your sanity check.

|||Sorry, Thank you.

Footer rows to skip

OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

.
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
.
0000260611

It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables.

Dima.

I too have the same type of flat files.

If u have solved this issue. Do help me please.

|||You can use another Data Flow to read the file a second time, with each row as a single column. Use a conditional split to identify the header/footer rows that you're interested in and derived columns (or script if necessary) to do any parsing before sending them to a destination. The downside here is that you have to read the file twice.

Alternatively, you can tackle the whole job in a single Data Flow with a script component that accepts the file rows as a single column, parses them in code, outputs the detail rows in discrete columns, and does whatever necessary for the header and footer. This is slightly more complicated from a code standpoint and the parsing logic could be a lot more tedious than letting the Flat File Source component do it.
|||Or perhaps read the file one time to get the number of rows in a variable and add a rownumber column to each row. Then in a second data flow use a conditional split to redirect the footer rows. This should work if you have a fixed number of rows in the footer.|||Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

|||

killerless wrote:

Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

Yeah. Read the other posts. And you can use a script component in the data flow to assign a value from the footer to a variable to that you can use it in your sanity check.

|||Sorry, Thank you.