ahh fer the luvva...

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

aggregate question

In the below structure, if I wanted to get the Id of the comment for
each Generic record having the latest comment time, how would I do that
not using a subquery?

Table: Generic
Id
Description

Table: Comment
Id
GenericId
CommentTime

Currently I have something like the following:

Select
Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
time comment record*/
From
Generic
INNER JOIN Comment ON Generic.Id = Comment.GenericId
Group By
Generic.Id

To get it, I could do a sub query, using the above query as its source
and joining on the max comment time, but I was wondering if there was a
way to do it without a sub query. Keep in mind that I am looking for a
set of Generic records and not looking for only a single record (so
select top top 1 with order by won't work)You need to take what you have and use it as a subquery as you've
suggested. Other than the subquery, there's no way in SQL to say "give
me the id column from the right table where some other column in the
right table has it's max value".

The reason you can't do that is simple: what id would you get back
from the right table if the maximum value occured in more than one row?|||On 3 Nov 2005 08:41:10 -0800, pb648174 wrote:

>In the below structure, if I wanted to get the Id of the comment for
>each Generic record having the latest comment time, how would I do that
>not using a subquery?
>Table: Generic
>Id
>Description
>Table: Comment
>Id
>GenericId
>CommentTime
>Currently I have something like the following:
>Select
> Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
>time comment record*/
>From
> Generic
> INNER JOIN Comment ON Generic.Id = Comment.GenericId
>Group By
> Generic.Id
>To get it, I could do a sub query, using the above query as its source
>and joining on the max comment time, but I was wondering if there was a
>way to do it without a sub query. Keep in mind that I am looking for a
>set of Generic records and not looking for only a single record (so
>select top top 1 with order by won't work)

Hi pb648174,

You can do this in two ways.

1. Using a correlated subquery (probably the solution you already had in
mind, since you write: "if there was a way to do it without a sub
query", but I'll give it anyway)

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN Comment AS c
ON c.GenericId = g.Id
WHERE c.CommentTime = (SELECT MAX(c2.CommentTime)
FROM Comment AS c2
WHERE c2.GenericId = c.GenericId)

2. Using a derived table. This is a subquery as well, but it's not
correlated, and it's used in the FROM clause, in place of a table or
view name:

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN (SELECT GenericId, MAX(CommentTime) AS MaxCommentTime
FROM Comment
GROUP BY GenericId) AS c2
ON c2.GenericId = g.Id
INNER JOIN Comment AS c
ON c.GenericId = g.Id
AND c.CommentTime = c2.MaxCommentTime

(Note: both queries are untested - see www.aspfaq.com/5006 if you prefer
a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you had done this right and realized tht ther are
no magical, universal "id' things in RDBMS, would the schema look like
this?

CREATE TABLE Generic
(generic_id INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(30) NOT NULL,
..);

CREATE TABLE Comments
(generic_id INTEGER NOT NULL
REFERENCES Generic (generic_id)
comment_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
-- where is the comment??
PRIMARY KEY (generic_id, comment_time));

>> but I was wondering if there was a way to do it without a subquery. <<

No, not unless you move comment time into Gerneric.|||Thanks, Celko. Helpful and on-topic as always.