Problem running package with 'larger' amount of data

Dear,

I created a package getting data from files and database sources, doing some transformations, retrieving dimension id's and then inserting it into a fact table.

Running this package with a limited amount of data (about a couple of 100.000 records) does not result in any errors and everything goes fine.

Now running the same package (still in debug mode) with more data (about 2.000.000 rows) doesn't result in any errors as well, but it just stops running. In fact, it doesn't really stop, but it doesn't continue as well. If I've only been waiting for some minutes or hours, I could think it's still processing, but I waited for about a day and it still is 'processing' the same step.

Any ideas on how to dig further into this in order to find the problem? Or is this a known problem?

Thanks for your ideas,

Jievie

Look at the BuffersSpooled perf counter. If it's really up there, you're thrashing the disk.

Also, just look task manager under the performance tab. Look at Physical Memory available. Chances are, you're running up against memory limitations.

Depending on what your pipeline contains, you should be able to optimize out the bottlenecks as well as eliminate any applications running that may be competing for resources.

This white paper is helpful:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

K

|||

Dear Jievie,

Could it be possible that the package tries to update a row locked by an other application?

Succes,

Paul

|||

Dear KirkHaselden and Paul,

In the meanwhile, I did some checks you both proposed me to do and can tell you the following:

I can't imagine another application is locking any rows or tables, as my sources are some flat files and two tables that are only used by my SSIS package. I also checked the system views (for the tables), no locks are listed...|||

There are three things you're not telling us ... how much memory you have, what transformations you are performing, and how big your rows are.

You are indicating you are memory constrained, but we don't know how much is involved.

The transformations are important - some transformations such as sort, or lookup, or aggregate, can require a lot of memory. Others, such as data conversion are very lightweight.

You can see the size of your rows by double clicking on the paths between transformations and looking at the metadata tab there - it will tell you what data types you are using for each row, and from there you can start to get the size of the row. (Remember folks, "row" is not a useful metric - we've seen rows from a few bytes to several megabytes being passed through SSIS.)

Once you get us this additional information, we should be able to help more. And do read the performance whitepaper - it will give you many useful suggestions.

thanks

Donald

Problem restoring SQL Svr 2000 Master db - wrong server version?

I'm trying to rebuild from a meltdown. I'm using disk backup files. I
could have sworn that I had SQL Server 2000 SP3 installed on the old
machine, but maybe not.

With SP3 installed, I get a message along the lines that the restore
can't be done because the backup was created using server version
134218262 and this server is version 134218488.

I've searched the documentation and can't find any reference to those
version numbers.

In any case, I thought SQL Server 2000 could restore backups created
on any SP level to any SP level.

Any ideas?

--
Regards.
Richard.On Tue, 13 Jan 2004 06:30:12 GMT, Richard Sherratt
<richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote:

>I'm trying to rebuild from a meltdown. I'm using disk backup files. I
>could have sworn that I had SQL Server 2000 SP3 installed on the old
>machine, but maybe not.

Ooop. Sorry. I had SP2 installed.

--
Regards.
Richard.|||"Richard Sherratt" <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote in
message news:dp7700dtggi3ev97mmgfgtacct926mjdb4@.4ax.com...
> On Tue, 13 Jan 2004 06:30:12 GMT, Richard Sherratt
> <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote:
> >I'm trying to rebuild from a meltdown. I'm using disk backup files. I
> >could have sworn that I had SQL Server 2000 SP3 installed on the old
> >machine, but maybe not.
> Ooop. Sorry. I had SP2 installed.

You mention the master DB in the subject.

I believe the system databases (Master, Model, MSDB) have to be restored to
the same level service pack they were backed up from.

Otherwise you're right. You can restore different SP levels of dbs to
different servers.

>
> --
> Regards.
> Richard.|||"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<eRSMb.72883$Dq1.66308@.twister.nyroc.rr.com>...
> "Richard Sherratt" <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote in
> message news:dp7700dtggi3ev97mmgfgtacct926mjdb4@.4ax.com...
> > On Tue, 13 Jan 2004 06:30:12 GMT, Richard Sherratt
> > <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote:
> > >I'm trying to rebuild from a meltdown. I'm using disk backup files. I
> > >could have sworn that I had SQL Server 2000 SP3 installed on the old
> > >machine, but maybe not.
> > Ooop. Sorry. I had SP2 installed.
> You mention the master DB in the subject.
> I believe the system databases (Master, Model, MSDB) have to be restored to
> the same level service pack they were backed up from.
> Otherwise you're right. You can restore different SP levels of dbs to
> different servers.

As part of a D&R test we restored databases to new servers, much the
same as you appear to be trying.

My best guess is that you have a different server name. We made sure
that the servers had the same name as the 'real' ones. If you are
dealing with a meltdown, this might actually help because your client
software won't have to be modified for the new server name.
>
> > --
> > Regards.
> > Richard.|||On Tue, 13 Jan 2004 13:59:38 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:

<snip
>You mention the master DB in the subject.
>I believe the system databases (Master, Model, MSDB) have to be restored to
>the same level service pack they were backed up from.
>Otherwise you're right. You can restore different SP levels of dbs to
>different servers.

Thanks. That would explain it.

--
Regards.
Richard.

Problem restoring a database

Can any of you give me a hint why we have problems with restoring a database. The database is 300 GB, divided on 4 database files in the primary filegroup and running in simple recovery mode. The restore starts and finish after few minutes saying 'Restore completed successful' but the database is in Loading mode.
Ergo, the restore is not successful. The only way we can restore the database is to drop the database and delete the files and create the database via the restore. But then it creates 4 data.MDF files and not 1 MDF and 3 NDF files and the name on the files are not the same as the original. Why?

We are running on a SQL 2000 SP4 but the backup was taken under SP3A

Regards Anna-LenaWhat does

RESTORE VERIFYONLY

This tell you...I'm thinking something not to nice...

That should be part of your back up procedures....And at 400 GB...that's a lot of data...I would also have a standby box to test the dump and restore immediatley after...how long does 400 GB take to dump?

Problem removing old back up files....

I am having problems with removing the update files from a folder. When
a do a new backup, I want it to overwrite the old backup files with the
new one. Apparently the code I am using is wrong. Any suggestions?Hi,
Would you be able to post your code so that people can see if they can
see anything with it or if it's fine?
If you are using BACKUP statements then you have to add "INIT" to the
"WITH" section. This will overwrite whatever backup device (e.g. the
file name) that you specify in the statement. For example:
BACKUP DATABASE Test_DB
TO DISK = 'C:\Test_DB_Backup.bak'
WITH INIT
WIll overwrite the previous contents of Test_DB_Backup.bak. Note that
there are some caveats with using INIT (namely that it won't overwrite
in certain situations) but these are described in SQL Server Books
Online in the topic about the BACKUP statement.
Hope that helps a bit|||EXECUTE master.dbo.xp_sqlmaint N'-PlanID
88FF9022-F638-4939-99FF-2FD57140382C -Rpt "C:\Program Files\Microsoft
SQL Server\MSSQL\LOG\<Backup Sever Name> Backup4.txt" -DelTxtRpt 2WEEKS
-WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\<Backup Server
Name>\Backup Do Not Touch\<SQL Server Name>" -DelBkUps 1DAYS
-CrBkSubDir -BkExt "BAK"'
There is the one we are using.|||OK, I see...There is an Database Maintenance Plan in place that will
take a backup of the desired database and delete backups older than one
day.
I don't believe that the sqlmaint utility (the utility that will be
called by xp_sqlmaint) has a switch that orders backups to be
overwritten. If you want to take backups but overwrite them each day
then you'll probably need to define your own SQL Server Agent job that
executes the following T-SQL as per whatever schedule you desire:
BACKUP DATABASE <DB_Name>
TO DISK = '<Path Of Backup File (includes the file name)>'
WITH INIT -- Force an overwrite most of the time (some caveats as
described in Books Online)
BTW, I think that generally it is a good idea to keep a few backup
files just in case something goes wrong with the backup you are
currently taking there is something to fall back on.
Hope that helps a bit

Problem recreating database

Had a crash and am trying to recreate my database. I have large files (200mb each)created using sql 7, a mdf and ldf file. When restoring on sql 2000 I get an internal consistency error trying to attach the mdf and ldf files. It fails half way through.

I desparetly need this database restored and would appreicate any help

Hi,

you could try to use the sp_attach_single_file_db and hope that this will be successfull. Seems that your database is inconsistent, do you have a valid backup ? That would be the best solution. Otherwise I hope for you that only attaching the MDF file with the above command works for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Problem of MSDE 2000 Release A Installation

Hi,
I'm new user for MSDE 2000 Release A.
Recently i have downloading MSDE2000A.exe and extract the MSDE 2000 Release
A installation files under folder name MSDERelA.
I have read thrg the document but still failed to install a new instance of
Desktop Engine for win XP .
Command Prompt:
C:\MSDERelA\Setup>SAPWD="xxxxx"
'SAPWD' is not recognized as an internal or external command,
operable program or batch file.
I think i'm missed interpreting the installation way.
Can anybody explain wht's the meaning of the below statement? Where can i
find
Windows Authentication Mode?
......To install a default instance configured to use Windows Authentication
Mode, execute: ..........
Appreciating any command suggestion for the above.
Thanks.
Regards
Chris
Try:
setup /sapwd xxxx
or put
[Options]
sapwd=xxxx
in setup.ini
SQL Server has multiple layers of security. The first one determines who can
talk to SQL Server (regardless of any attached databases). Windows
Authentication, the new default, lets anyone with a Windows login talk to
SQL Server. The old method, called SQL Server Authentication, requires a
user name and password instead. There exists Mixed Mode Authentication,
which will allow both, for instance in case you want to allow some user over
the network to access some parts of a database, you first have to allow him
to talk to SQL Server. Windows Authentication is the recommended mode,
unless you have a reason to allow login by name and password.
I'm rather new to all this, so some things I've told you might be wrong.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:93E4793E-ACAA-4AE1-B0DE-B0C341B76111@.microsoft.com...
> Hi,
> I'm new user for MSDE 2000 Release A.
> Recently i have downloading MSDE2000A.exe and extract the MSDE 2000
> Release
> A installation files under folder name MSDERelA.
> I have read thrg the document but still failed to install a new instance
> of
> Desktop Engine for win XP .
> Command Prompt:
> C:\MSDERelA\Setup>SAPWD="xxxxx"
> 'SAPWD' is not recognized as an internal or external command,
> operable program or batch file.
> I think i'm missed interpreting the installation way.
> Can anybody explain wht's the meaning of the below statement? Where can i
> find
> Windows Authentication Mode?
> .....To install a default instance configured to use Windows
> Authentication
> Mode, execute: ..........
> Appreciating any command suggestion for the above.
> Thanks.
> Regards
> Chris
>
>
>
>
>
|||Thanks for the explaination. I have tried again with the command-
C:\MSDERelA\Setup>Setup/ SAPWD xxxxx
C:\MSDERelA\Setup>
System Prompt msg "pls go to control panel to install &configure your sys
component". Any idea what does it mean?
Below are the files in folder MSDERelA:
-- folder Msi, folder Setup (sql window installer pcks/patches),
autorun.txt, license.txt, ReadmeMSDE2000A.htm, setup.exe, setup.rll &
sqlresld.dll
Did i wrote a command to a wrong file?
Anybody can help to suggest a new command?
Thanks
Chris
"Paul Pedersen" wrote:

> Try:
> setup /sapwd xxxx
> or put
> [Options]
> sapwd=xxxx
> in setup.ini
>
> SQL Server has multiple layers of security. The first one determines who can
> talk to SQL Server (regardless of any attached databases). Windows
> Authentication, the new default, lets anyone with a Windows login talk to
> SQL Server. The old method, called SQL Server Authentication, requires a
> user name and password instead. There exists Mixed Mode Authentication,
> which will allow both, for instance in case you want to allow some user over
> the network to access some parts of a database, you first have to allow him
> to talk to SQL Server. Windows Authentication is the recommended mode,
> unless you have a reason to allow login by name and password.
> I'm rather new to all this, so some things I've told you might be wrong.
>
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:93E4793E-ACAA-4AE1-B0DE-B0C341B76111@.microsoft.com...
>
>
|||hi,
Paul Pedersen wrote:
> Try:
> setup /sapwd xxxx
>
just a typo...
SAPWD=xxxx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I got that message too. I was running setup from a network drive. When I ran
it from a local drive, it worked.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4DE8D5D0-9AC8-4837-8AA6-88DF10BBB921@.microsoft.com...[vbcol=seagreen]
> Thanks for the explaination. I have tried again with the command-
> C:\MSDERelA\Setup>Setup/ SAPWD xxxxx
> C:\MSDERelA\Setup>
> System Prompt msg "pls go to control panel to install &configure your sys
> component". Any idea what does it mean?
> Below are the files in folder MSDERelA:
> -- folder Msi, folder Setup (sql window installer pcks/patches),
> autorun.txt, license.txt, ReadmeMSDE2000A.htm, setup.exe, setup.rll &
> sqlresld.dll
> Did i wrote a command to a wrong file?
> Anybody can help to suggest a new command?
> Thanks
> Chris
> "Paul Pedersen" wrote:
|||Hi,
I ran it from a local drive. Unfortunately, the same msg still being
encounter.
C:\MSDERelA\Setup>Setup SAPWD=" xxxxx"[vbcol=seagreen]
Any hint will be sincerly appreciated..
Regards
Chris
"Paul Pedersen" wrote:

> I got that message too. I was running setup from a network drive. When I ran
> it from a local drive, it worked.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:4DE8D5D0-9AC8-4837-8AA6-88DF10BBB921@.microsoft.com...
>
>
|||I have no other experience with that error message, but I when I ran an
internet search on the error message phrase, I got several hits. None of
them helped my situation, but maybe they will help yours.
In my case, I think it was something about Setup not finding network drives
or something like that. Does it work if you run setup with no parameters?
When I tried it, I used only
setup.exe /settings settings.ini
and kept all the switches in the ini file.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DED2A104-30C5-4461-9EB6-9FD44B4DDC5B@.microsoft.com...[vbcol=seagreen]
> Hi,
> I ran it from a local drive. Unfortunately, the same msg still being
> encounter.
> C:\MSDERelA\Setup>Setup SAPWD=" xxxxx"
> Any hint will be sincerly appreciated..
> Regards
> Chris
>
>
> "Paul Pedersen" wrote:
|||hi,
Paul Pedersen wrote:
> In my case, I think it was something about Setup not finding network
> drives or something like that. Does it work if you run setup with no
> parameters? When I tried it, I used only
>
are File and Printer sharing set in your network configuration?
is the Server service running in your control panel->admin tools->services ?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Mine is working OK now, thank you. It's Chris who is still having a problem.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3hoakvFi25ieU1@.individual.net...
> hi,
> Paul Pedersen wrote:
> are File and Printer sharing set in your network configuration?
> is the Server service running in your control panel->admin tools->services
> ?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi,
Appreciating your guys help on my case. I tried, althg still failed to
install it. For the case, I’ll thinking to purchase a pc of Visual.net.
have a nice day's.
Regards
Chris
=======
Regards
Chris
"Paul Pedersen" wrote:

> Mine is working OK now, thank you. It's Chris who is still having a problem.
>
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3hoakvFi25ieU1@.individual.net...
>
>

Problem loading XML Files

I have been trying unsuccessfully to load a directory full of XML files into SQL Server 2005 Standard. I have not been able to make it work, and I would be grateful for any advice.

A simple version of the files that I am trying to load is TEST.NWS just below. I have removed almost all of the content from the files, leaving as a file TEST.NWS. (Hopefully I have not cut out too much.) This version of the test file leaves me with the same error messages I have with the full file. I have a directory with many such files and each file is much too big to edit manually.

Start TEST.NWS--

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC001">

</NewsComponent>

</NewsML>

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC002">

</NewsComponent>

</NewsML>

END-

The schema I am using is here: http://www.newsml.org/dl.php?fn=NewsML/1.2/specification/NewsML_1.2.xsd

I also tried this schema: http://www.nitf.org/IPTC/NITF/3.3/specification/schema/nitf-3-3.xsd

One version of the steps that I follow are here: http://msdn2.microsoft.com/en-us/library/ms139886.aspx At stage 8 of the "how to" recipe, when I click on the Columns, I get an error. The wording of the error depends on which of the two schemas I use. If I instead click next I also get an error. Clearly I am doing something wrong. Any suggestion for how to fix this will be most welcome. Thanks.

mzfrank,

Please you please copy and paste the error messages you are getting and optional the T-SQL script you are running?

Thanks,

Galex

|||

I was not using a script. I was following the MSDN "How to: Extract Data Using the XML Source" it uses SSIS rather than a T-SQL script. (Is this a task better done using T-SQL rather than SSIS?)

If I use NewsML_1.2.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: There was an error setting up the mapping. The 'http://www.w3.org/XML/1998/namespace:lang' attribute is not declared.

If I use nitf-3-3.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: The XML Source Adapter does not support mixed content model on Complex Types.

If I ask it to infer a schem I get:

Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio)

|||

I am having the same issue. I posted in the SSIS forum, but this is what I wrote there:

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

Problem installing setup support files

Hi,
trying to install Reporting Services Enterprise Ed. on a server machine with
configuration:
- Win 2003 Enterprise with SP1
- MS SQL 2000 Server with SP4
- IIS 6 with ASP.NET 1.1
Problem is that when I execute Setup.exe I receive an message: "Error!"
after only 2-3 seconds and thats it! No more info on what the error is or so.
Only option for me is to click Cancel and the installation ends.
This happens when setup tries to install Microsoft SQL Server Reporting
Services Setup Support Files which is the first to happen.
Please, anyone have an idea what this can be or even how to track down the
error?
No errors are reported in Event Viewer.
I have rerun aspnet_regiis just in case, witout any result.
Thanks.Found the problem!
I was running the installation from my local DVD logged in to the server via
Remote Desktop and my local disks shared.
Copied the installationfiles to server harddisk and it installed!
"Gizmo Gizmo" wrote:
> Hi,
> trying to install Reporting Services Enterprise Ed. on a server machine with
> configuration:
> - Win 2003 Enterprise with SP1
> - MS SQL 2000 Server with SP4
> - IIS 6 with ASP.NET 1.1
> Problem is that when I execute Setup.exe I receive an message: "Error!"
> after only 2-3 seconds and thats it! No more info on what the error is or so.
> Only option for me is to click Cancel and the installation ends.
> This happens when setup tries to install Microsoft SQL Server Reporting
> Services Setup Support Files which is the first to happen.
> Please, anyone have an idea what this can be or even how to track down the
> error?
> No errors are reported in Event Viewer.
> I have rerun aspnet_regiis just in case, witout any result.
> Thanks.