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 Andrea...Install MSDE w/ MSDE Depl.Toolkit. cont.

Hi Andrea,
our thread seems to have stopped. i'd appreciate it if you have the
time to continue here.
To recap here is my last question, any help appreciated,
Regards
Andrew
Hi Andrea
I understand what you mean about groups now (I hope). Unless I use a
group containing ONE user - the special user NTAuthority\Authenticated
users, then I can't get away from adding each user individually (unless
I user SQL Server authentication), even with groups. Is this correct?
I got a test user (lets call them somedomain\testuser) to use osql to
run the sql code you suggested. It ran fine, but it didn't return
"guest"! Instead it returned "dbo" which surprised me as the dbo of the
database on the server (where this MSDE database came from) is me, not
somedomain\testuser. I checked on the server and it clearly states that
the owner of the database is me. Maybe its because of how the database
is created in the first place on the users machine? Here is the code
that my installation routine uses to install the database:
/* If the abcdb database exists, then drop it */
IF EXISTS (SELECT *
FROM sysdatabases
WHERE name = 'abcdb')
DROP DATABASE abcdb
GO
/* Attach mdf and ldf to new database */
exec sp_attach_db @.dbname=N'abcdb',
@.filename1 = N'c:\abcsqldata\abcdb.MDF',
@.filename2 = N'c:\abcsqldata\abcdb.LDF'
Thanks
Andrew
Andrea Montanari wrote:[vbcol=seagreen]
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1101991621.708649.228870@.c13g2000cwb.googlegr oups.com
this.[vbcol=seagreen]
but[vbcol=seagreen]
for[vbcol=seagreen]
a
> :D that's what I (wanted to) suggest... to maintain your WinNT logins
as
> groups (I know "MyApplicationUsers" or "TravellingUsers" do not
usually
> exists as you have to create that groups :D... I apologize for
beeing[vbcol=seagreen]
> cryptic )
return[vbcol=seagreen]
in
> try executing
> SET NOCOUNT ON
> SELECT CURRENT_USER AS [current user]
> which should report
> --<--
> current user
> --
> guest
> that's to say MyDomain\MyUser is mapped to "Guest" user (if you did
not
> removed it from your model database as I usually do) and will be
>member of
> the "Public" database role...
hi Andrew,
"CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
news:1102930491.966945.82510@.c13g2000cwb.googlegro ups.com
> Hi Andrea,
> our thread seems to have stopped. i'd appreciate it if you have the
> time to continue here.
> To recap here is my last question, any help appreciated,
I apologize... my wife wanted me for vacation :D

> Regards
> Andrew
> Hi Andrea
> I understand what you mean about groups now (I hope). Unless I use a
> group containing ONE user - the special user NTAuthority\Authenticated
> users, then I can't get away from adding each user individually
> (unless I user SQL Server authentication), even with groups. Is this
> correct?
you only need adding new WinNT user(s) to your (SQL Server login granted)
WinNT groups...

> I got a test user (lets call them somedomain\testuser) to use osql to
> run the sql code you suggested. It ran fine, but it didn't return
> "guest"! Instead it returned "dbo" which surprised me as the dbo of
> the database on the server (where this MSDE database came from) is
> me, not somedomain\testuser. I checked on the server and it clearly
> states that the owner of the database is me. Maybe its because of how
> the database is created in the first place on the users machine? Here
> is the code that my installation routine uses to install the database:
> /* If the abcdb database exists, then drop it */
> IF EXISTS (SELECT *
> FROM sysdatabases
> WHERE name = 'abcdb')
> DROP DATABASE abcdb
> GO
> /* Attach mdf and ldf to new database */
> exec sp_attach_db @.dbname=N'abcdb',
> @.filename1 = N'c:\abcsqldata\abcdb.MDF',
> @.filename2 = N'c:\abcsqldata\abcdb.LDF'
perhaps your somedomain\testuser is member of dbowner database rose and/or
part of the sysadmins server role...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
thanks for the response.

> perhaps your somedomain\testuser is member of dbowner database rose
and/or
> part of the sysadmins server role...
No, he's not a member of either.
However on the server, If I select the server role sysadmin, I see that
BUILTIN\Administrators is a member. somedomain\testuser has admin
rights on his local PC, so is this why he becomes the owner of the MSDE
database when it is installed locally?
Regards
Andrew
Andrea Montanari wrote:[vbcol=seagreen]
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1102930491.966945.82510@.c13g2000cwb.googlegro ups.com
> I apologize... my wife wanted me for vacation :D
a[vbcol=seagreen]
NTAuthority\Authenticated[vbcol=seagreen]
this
> you only need adding new WinNT user(s) to your (SQL Server login
granted)[vbcol=seagreen]
> WinNT groups...
to[vbcol=seagreen]
how[vbcol=seagreen]
Here[vbcol=seagreen]
database:
> perhaps your somedomain\testuser is member of dbowner database rose
and/or
> part of the sysadmins server role...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a
visual
> interface)
> -- remove DMO to reply
|||hi Andrew,
"CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
news:1103021537.911855.37930@.c13g2000cwb.googlegro ups.com
> Andrea
> thanks for the response.
>
> No, he's not a member of either.
> However on the server, If I select the server role sysadmin, I see
> that BUILTIN\Administrators is a member. somedomain\testuser has admin
> rights on his local PC, so is this why he becomes the owner of the
> MSDE database when it is installed locally?
if somedomain\testuser is member of BUILTIN\Administrators the he/she is for
sure member of sysadmins (local) server role..
he/she will not become owner of the attached database, as the owner will
remain the original one, but he/she will be able to create new databases as
long as attach detached ones...
usually it's not a good idea to log on as (local) admin on client pc
workstations too :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
hmm, I wonder why somedomain\testuser becomes the dbo then? This means
the owner of the database doesn't it? When I installed the database on
my test (local) client, and ran the osql code, it showed me as the dbo.
However when my colleague did the same on his test local client, it
showed him as the dbo!
Regards
Andrew
Andrea Montanari wrote:[vbcol=seagreen]
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1103021537.911855.37930@.c13g2000cwb.googlegro ups.com
rose[vbcol=seagreen]
admin
> if somedomain\testuser is member of BUILTIN\Administrators the he/she
is for
> sure member of sysadmins (local) server role..
> he/she will not become owner of the attached database, as the owner
will
> remain the original one, but he/she will be able to create new
databases as
> long as attach detached ones...
> usually it's not a good idea to log on as (local) admin on client pc
> workstations too :D
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a
visual
> interface)
> -- remove DMO to reply
|||hi Andrew,
"CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
news:1103106240.866306.135020@.f14g2000cwb.googlegr oups.com
> Hi Andrea
> hmm, I wonder why somedomain\testuser becomes the dbo then? This means
> the owner of the database doesn't it? When I installed the database on
> my test (local) client, and ran the osql code, it showed me as the
> dbo. However when my colleague did the same on his test local client,
> it showed him as the dbo!
if somedomain\testuser is (locally) member of the Builtin\Administrators
role, he is member of the local (MSDE) server sysadmin server role... he
will be no be member of the sysadmin server role on the main SQL Server
instance residing on the central server..
as (local) sysadmin, he will be member and will be listed as dbowner of
every database residing on the MSDE instance even if the corresponding login
has not be granted access/privileges to them as, as sysadmin, he is really a
God (ok, a little and strange kind of god :D) on the local MSDE instance...
your colleague, as somedomain\some_other_testuser , on his pc's MSDE
instance, as member of his Builtin\Administrators role, will be the same on
his MSDE instance, while perhaps being a standard login (member of
somedomain\somegroup) on your MSDE instance and on the central SQL Server
instance..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
ok that's great! Thanks for all your help and patience.
Bye for now.
Andrew.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:32b14vF3k7sa0U1@.individual.net...
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1103106240.866306.135020@.f14g2000cwb.googlegr oups.com
> if somedomain\testuser is (locally) member of the Builtin\Administrators
> role, he is member of the local (MSDE) server sysadmin server role... he
> will be no be member of the sysadmin server role on the main SQL Server
> instance residing on the central server..
> as (local) sysadmin, he will be member and will be listed as dbowner of
> every database residing on the MSDE instance even if the corresponding
login
> has not be granted access/privileges to them as, as sysadmin, he is really
a
> God (ok, a little and strange kind of god :D) on the local MSDE
instance...
> your colleague, as somedomain\some_other_testuser , on his pc's MSDE
> instance, as member of his Builtin\Administrators role, will be the same
on
> his MSDE instance, while perhaps being a standard login (member of
> somedomain\somegroup) on your MSDE instance and on the central SQL Server
> instance..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>