Bad data in source file kills package?

I'm reading data from sets of CSV source files with " text qualifiers. Most of the data is good, but I do run into some bad data that doesn't seem to get handled by redirecting rows. The type of data that is killing me is this:

"Business ID","Business Name","Business Phone"
"12345",""A" SPOT, THE ","555-555-5555"

Access sees this as an unparsable record and continues with the rest of the data. SSIS seems to choke on it. I've set the package to allow 1000 errors with no change.

I did set up an error file from my source file, with error output set to redirect row on error and on truncation. The error output seems only to show columns for "Flat File Source Error Output Column", "ErrorCode", and "ErrorColumn" too. (I'm sure there's a good reason, but I've found it really frustrating that it doesn't use the source file column mappings.)

What I'd like to do is redirect (or fix) the bad rows and continue with the rest of the load.

The Execution Results tab shows the following errors.

[Flat File Source [2082]] Error: The column delimiter for column "Business Phone" was not found.
[Flat File Source [2082]] Error: An error occurred while processing file "\\Share\Files\flat_file.csv" on data row 464.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (2082) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help would be much appreciated!

two years past...I encounter the same problem.

Just output the source file to other format: tab as delimiter, execl..... to skip the problem.

|||Hi lund,

This is because you were using " as text qualifier and there was a data that had value just the same as the text qualifier. The solution is you should replace the " of your data value with another character like '. Or just use another text qualifier.

Best regards,

Hery|||

The other two posters are correct. If you have any control over the source file consider changing the delimeter (to tabs) and/or the text qualifier (to something that doesn't appear in your text.)

Failing that, I have had some success in the past in pre-processing files. Basically you can load the file line by line, treating each line as a single record, and do some processing on the line to clean it.

Then you output the clean lines back into a new file.

Processing could be simple (eg count the number of commas in a row, or verify that if you split a row you get a set number of columns, and they are the correct width.)

Preferred approach is to fix the source data to export to something other than comma separated, though. There is a good reason that .tab files were invented!

Dylan.

Backups with Maintenance Plans

Hi
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJHi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>|||... and, of course, carefully consider whether you also want to do more frequent transaction log
backups. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:CE066399-68A6-469E-BBCC-2792BF430D85@.microsoft.com...
> Yeah, for only 2 GB do daily full backups including daily backups of your
> system databases.
> Hope this helps,
> Ben Nevarez
>
>
> "TJ" wrote:
>> ok, thanks
>> Yeah, the backups are about 2 Gigs, so I could just do them as daily
>> full backups.

Backups with Maintenance Plans

Hi
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJ
Hi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:

> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>
|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.
|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:

> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>

Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.

>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
>
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing u
p
straight to the remote file share, if it fails towards to the end due to som
e
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:

> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
>
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>

Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.
>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
>>From a network performance standpoint I think backing up locally and
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing up
straight to the remote file share, if it fails towards to the end due to some
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> >From a network performance standpoint I think backing up locally and
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>

Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.

>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)
vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing up
straight to the remote file share, if it fails towards to the end due to some
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:

> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>