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

AggMerge_ .TMP files eat my harddisk

Hi champs;

This question has already been asked; but still no answer yet on this issue.

When I process the partition I can see that the aggregations are being created. When I look in the file system I see a bunch of .tmp files being created (e.g. AggMerge_5800_54_8s64d_28.tmp), all of them for about 1.0GB. I eventually run out of space on the drive and the processing fails.

All together these .tmp files take about 150GB. The source database is only about 200GB.

Any thoughts? Ideas? The aggregation design wizard tells me the aggregations will take up about 125kb, but the .tmp files tell me something different.

Thanks

kurlan

There are several points here.

One is. You need to take a look at the partition size. You might need to create smaller partitions.

Second. Aggregation design algorithm cannot calculate the size of your aggregations. It only makes size calculations based on the estimated counts on dimension attributes. You might want to create fewer aggregations.

Third. The reason you are seeing temporary files created is: All the aggregations you've designed cannot fit into memory and they are swapped to disc. By decreasing number of aggregations and by making sure you process fewer partitions in parallel, you should be able to achieve a point when aggregation processing does not require swapping.

Here is good source of information about best pracices for partitioning and aggregation creation: http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running on
a new server and have the AGENT running under a local server username - not a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps?
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do?
The SQLServerAgent runs under a domain account for us, that has writes to all
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:

> I'm asking this question for a client of mine. They have SQL 2005 running on
> a new server and have the AGENT running under a local server username - not a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not have
> access to the share. How do you get more un's listed for the agent steps?
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do?

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running on
a new server and have the AGENT running under a local server username - not a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps'
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do'The SQLServerAgent runs under a domain account for us, that has writes to all
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:
> I'm asking this question for a client of mine. They have SQL 2005 running on
> a new server and have the AGENT running under a local server username - not a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not have
> access to the share. How do you get more un's listed for the agent steps'
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do'

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running o
n
a new server and have the AGENT running under a local server username - not
a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps'
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do'The SQLServerAgent runs under a domain account for us, that has writes to al
l
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:

> I'm asking this question for a client of mine. They have SQL 2005 running
on
> a new server and have the AGENT running under a local server username - no
t a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not ha
ve
> access to the share. How do you get more un's listed for the agent steps?
?
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do'

After seeing AS/400, am i brainwashed by MS SQL Server?

IBM's AS/400 has:
"Libraries" instead of "Databases"
"Files" instead of "Tables"
"Logicals" instead of "Views"
and field names seem to be limited to 6 characters. So field names like
LNM@.CN (i.e. LastName) are not uncommon.
Am i being really closed minded about AS/400; or is what it's doing
perfectly valid and common?
i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
And how the heck is anyone supposed to know that the "file" CFPCLMM is
actually the table "Club Membership Information", and MSTSMM is a field name
for "Member Status"? And that the "logical" CFLDPCN1 is a view?
Is AS/400 stupid, or am i, or a little bit of both?<<
CFLDPCN1
I did an AS/400 to SQL DW a few years ago and drove myself nuts trying to
figure out thier non-verbose column names. Strangley enough the AS400 people
who started at it all day for years seemed to know... but I don't think
you're missing anything. An AS400 is certainly a powerful DB but it's not
quite as easy to use as SQL Server,
--
Brian
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:e%237oWhHVEHA.2840@.TK2MSFTNGP11.phx.gbl...
> IBM's AS/400 has:
> "Libraries" instead of "Databases"
> "Files" instead of "Tables"
> "Logicals" instead of "Views"
> and field names seem to be limited to 6 characters. So field names like
> LNM@.CN (i.e. LastName) are not uncommon.
> Am i being really closed minded about AS/400; or is what it's doing
> perfectly valid and common?
> i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
> And how the heck is anyone supposed to know that the "file" CFPCLMM is
> actually the table "Club Membership Information", and MSTSMM is a field
name
> for "Member Status"? And that the "logical" CFLDPCN1 is a view?
>
> Is AS/400 stupid, or am i, or a little bit of both?
>

After seeing AS/400, am i brainwashed by MS SQL Server?

IBM's AS/400 has:
"Libraries" instead of "Databases"
"Files" instead of "Tables"
"Logicals" instead of "Views"
and field names seem to be limited to 6 characters. So field names like
LNM@.CN (i.e. LastName) are not uncommon.
Am i being really closed minded about AS/400; or is what it's doing
perfectly valid and common?
i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
And how the heck is anyone supposed to know that the "file" CFPCLMM is
actually the table "Club Membership Information", and MSTSMM is a field name
for "Member Status"? And that the "logical" CFLDPCN1 is a view?
Is AS/400 stupid, or am i, or a little bit of both?<<
CFLDPCN1
I did an AS/400 to SQL DW a few years ago and drove myself nuts trying to
figure out thier non-verbose column names. Strangley enough the AS400 people
who started at it all day for years seemed to know... but I don't think
you're missing anything. An AS400 is certainly a powerful DB but it's not
quite as easy to use as SQL Server,
--
Brian
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:e%237oWhHVEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> IBM's AS/400 has:
> "Libraries" instead of "Databases"
> "Files" instead of "Tables"
> "Logicals" instead of "Views"
> and field names seem to be limited to 6 characters. So field names like
> LNM@.CN (i.e. LastName) are not uncommon.
> Am i being really closed minded about AS/400; or is what it's doing
> perfectly valid and common?
> i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
> And how the heck is anyone supposed to know that the "file" CFPCLMM is
> actually the table "Club Membership Information", and MSTSMM is a field
name
> for "Member Status"? And that the "logical" CFLDPCN1 is a view?
>
> Is AS/400 stupid, or am i, or a little bit of both?
>

After seeing AS/400, am i brainwashed by MS SQL Server?

IBM's AS/400 has:
"Libraries" instead of "Databases"
"Files" instead of "Tables"
"Logicals" instead of "Views"
and field names seem to be limited to 6 characters. So field names like
LNM@.CN (i.e. LastName) are not uncommon.
Am i being really closed minded about AS/400; or is what it's doing
perfectly valid and common?
i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
And how the heck is anyone supposed to know that the "file" CFPCLMM is
actually the table "Club Membership Information", and MSTSMM is a field name
for "Member Status"? And that the "logical" CFLDPCN1 is a view?
Is AS/400 stupid, or am i, or a little bit of both?
<<
CFLDPCN1[vbcol=seagreen]
I did an AS/400 to SQL DW a few years ago and drove myself nuts trying to
figure out thier non-verbose column names. Strangley enough the AS400 people
who started at it all day for years seemed to know... but I don't think
you're missing anything. An AS400 is certainly a powerful DB but it's not
quite as easy to use as SQL Server,
Brian
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:e%237oWhHVEHA.2840@.TK2MSFTNGP11.phx.gbl...
> IBM's AS/400 has:
> "Libraries" instead of "Databases"
> "Files" instead of "Tables"
> "Logicals" instead of "Views"
> and field names seem to be limited to 6 characters. So field names like
> LNM@.CN (i.e. LastName) are not uncommon.
> Am i being really closed minded about AS/400; or is what it's doing
> perfectly valid and common?
> i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
> And how the heck is anyone supposed to know that the "file" CFPCLMM is
> actually the table "Club Membership Information", and MSTSMM is a field
name
> for "Member Status"? And that the "logical" CFLDPCN1 is a view?
>
> Is AS/400 stupid, or am i, or a little bit of both?
>

After DBCC SHRINKFILE

After DBCC SHRINKFILE(dbname_Log,10)
The message:
“Cannot shrink log file 2 (dbname_Log) because all logical log files are i
n
use.”
had been appeared.
How to decrease the log file size ?Did you backup the transaction log before trying to shrink it?
AMB
"Alur" wrote:

> After DBCC SHRINKFILE(dbname_Log,10)
> The message:
> “Cannot shrink log file 2 (dbname_Log) because all logical log files are
in
> use.”
> had been appeared.
> How to decrease the log file size ?
>|||... and sometimes you have to do this several times (backup, shrink, backup
, shrink...) Check out
DBCC LOGINFO (Google etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:67DA0AAF-F3F6-4AA4-8C55-4F86F916A02E@.microsoft.com...
> Did you backup the transaction log before trying to shrink it?
>
> AMB
> "Alur" wrote:
>|||I could not backup the transaction log.
"Alejandro Mesa" wrote:
> Did you backup the transaction log before trying to shrink it?
>
> AMB
> "Alur" wrote:
>|||What error are you getting when trying to backup the transaction log?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:3B228137-B620-408E-9F8A-435F00286FAA@.microsoft.com...
>I could not backup the transaction log.
> "Alejandro Mesa" wrote:
>|||Something about insufficient disk space. And I cannot increase the space now
.
"Mike Epprecht (SQL MVP)" wrote:

> What error are you getting when trying to backup the transaction log?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:3B228137-B620-408E-9F8A-435F00286FAA@.microsoft.com...
>
>