Basic package is causing an error

I'm getting the following error message on a basic copy from a datareader (using an ODBC datasource) to a sqlnativeclient. There are no transformations or anything. Don't know what is going on. Any insights are appreciated.

[SQL Server Destination [361]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

|||

Jamie Thomson wrote:

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

jamie is correct. you can use the ole db destination instead.

btw, in her ssis webcast, joy mundy said that the ole db destination outperforms the sql server destination (in most cases).

|||

Duane Douglas wrote:

Jamie Thomson wrote:

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

jamie is correct. you can use the ole db destination instead.

btw, in her ssis webcast, joy mundy said that the ole db destination outperforms the sql server destination (in most cases).

Really? Then Joy Mundy is wrong. Please could you send me a link to the webcast, I need to check this out?

Regards

Jamie

|||Thank you Jamie and Duane. Makes sense, but I didn't realize you had to use OLE DB for non-local connections. I'll go that route.|||

OLE DB Destination is the "default" destination for any relational database table. The SQL Server Destination is an optional extra that can be used in very specific circumstances.

Personally I think the name "SQL Server Destination" confuses people. They see it and automatically think it should be used when inserting into SQL Server and that is not the case. In Microsoft's defence it does say this very very clearly in the documentation.

-Jamie

|||I'm sure it is documented, but as often happens in life I have to fly by the seat of my pants learning as I go for a quick solution to a problem. No time to actually learn the product till later. Again, the help is greatly appreciated.|||

Jamie Thomson wrote:

Duane Douglas wrote:

Jamie Thomson wrote:

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

jamie is correct. you can use the ole db destination instead.

btw, in her ssis webcast, joy mundy said that the ole db destination outperforms the sql server destination (in most cases).

Really? Then Joy Mundy is wrong. Please could you send me a link to the webcast, I need to check this out?

Regards

Jamie

jamie,

here's the link to joy's webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032297072&EventCategory=5&culture=en-US&CountryCode=US

|||

Jamie Thomson wrote:

Personally I think the name "SQL Server Destination" confuses people. They see it and automatically think it should be used when inserting into SQL Server and that is not the case. In Microsoft's defence it does say this very very clearly in the documentation.

-Jamie

i agree. "local sql server destination" might be a more appropriate title.sql

Basic DTS...

Hi all,

am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:

1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)

In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!

Thanks,
Elisabet--call ur dts package from a sp, something like,
if(@.somecondition)
exec master..xp_cmd_shell 'DTSRun /S MySQLServerName /E /N "Package Name"'
else
break

Bandwidth Utilization...

I have two identical SQL Servers... same OS... same config... when i
run my SSIS package on one server... Bandwidth utilization goes upto
20 mb... and package completes in 10 mins. Its getting data from
oracle DB. I will be migrating this package to another the other
server... there it takes about 1 hour... and bandwidth utilization is
about 2mb... why is the difference in speed of getting data? What
can be the contributor for this? I am using oracle 9i drivers for both
servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
9.2.0.6 (2mb max). I dont think it can be the driver version. However
I cant seem to find the 9.2.0.0 to rule out this possibility.
Any help will be greatly appreciated.
Regards, zomer.
How are the NIC's configured? Is one configured at 10MB? What about the other
NIC settings?
Have you checked the event log on the machine that is getting low
utilization? Anything there?
I don't know what its called, but I know that the network guys can limit the
bandwidth someone uses. Ask your network guys and see if its been done.
Hope that helps,
Jay
"zomer" wrote:

> I have two identical SQL Servers... same OS... same config... when i
> run my SSIS package on one server... Bandwidth utilization goes upto
> 20 mb... and package completes in 10 mins. Its getting data from
> oracle DB. I will be migrating this package to another the other
> server... there it takes about 1 hour... and bandwidth utilization is
> about 2mb... why is the difference in speed of getting data? What
> can be the contributor for this? I am using oracle 9i drivers for both
> servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
> 9.2.0.6 (2mb max). I dont think it can be the driver version. However
> I cant seem to find the 9.2.0.0 to rule out this possibility.
> Any help will be greatly appreciated.
> Regards, zomer.
>
|||Both are at 100 Full Duplex. That is the Speed of the OAM router.
That OAM router is locked on 100 full also.
Everything else is the same on both machines... except the ora driver
version.
sql

Bandwidth Utilization...

I have two identical SQL Servers... same OS... same config... when i
run my SSIS package on one server... Bandwidth utilization goes upto
20 mb... and package completes in 10 mins. Its getting data from
oracle DB. I will be migrating this package to another the other
server... there it takes about 1 hour... and bandwidth utilization is
about 2mb... why is the difference in speed of getting data? What
can be the contributor for this? I am using oracle 9i drivers for both
servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
9.2.0.6 (2mb max). I dont think it can be the driver version. However
I cant seem to find the 9.2.0.0 to rule out this possibility.
Any help will be greatly appreciated.
Regards, zomer.How are the NIC's configured? Is one configured at 10MB? What about the other
NIC settings?
Have you checked the event log on the machine that is getting low
utilization? Anything there?
I don't know what its called, but I know that the network guys can limit the
bandwidth someone uses. Ask your network guys and see if its been done.
Hope that helps,
Jay
"zomer" wrote:
> I have two identical SQL Servers... same OS... same config... when i
> run my SSIS package on one server... Bandwidth utilization goes upto
> 20 mb... and package completes in 10 mins. Its getting data from
> oracle DB. I will be migrating this package to another the other
> server... there it takes about 1 hour... and bandwidth utilization is
> about 2mb... why is the difference in speed of getting data? What
> can be the contributor for this? I am using oracle 9i drivers for both
> servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
> 9.2.0.6 (2mb max). I dont think it can be the driver version. However
> I cant seem to find the 9.2.0.0 to rule out this possibility.
> Any help will be greatly appreciated.
> Regards, zomer.
>|||Both are at 100 Full Duplex. That is the Speed of the OAM router.
That OAM router is locked on 100 full also.
Everything else is the same on both machines... except the ora driver
version.

Bandwidth Utilization...

I have two identical SQL Servers... same OS... same config... when i
run my SSIS package on one server... Bandwidth utilization goes upto
20 mb... and package completes in 10 mins. Its getting data from
oracle DB. I will be migrating this package to another the other
server... there it takes about 1 hour... and bandwidth utilization is
about 2mb... why is the difference in speed of getting data? What
can be the contributor for this? I am using oracle 9i drivers for both
servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
9.2.0.6 (2mb max). I dont think it can be the driver version. However
I cant seem to find the 9.2.0.0 to rule out this possibility.
Any help will be greatly appreciated.
Regards, zomer.How are the NIC's configured? Is one configured at 10MB? What about the othe
r
NIC settings?
Have you checked the event log on the machine that is getting low
utilization? Anything there?
I don't know what its called, but I know that the network guys can limit the
bandwidth someone uses. Ask your network guys and see if its been done.
Hope that helps,
Jay
"zomer" wrote:

> I have two identical SQL Servers... same OS... same config... when i
> run my SSIS package on one server... Bandwidth utilization goes upto
> 20 mb... and package completes in 10 mins. Its getting data from
> oracle DB. I will be migrating this package to another the other
> server... there it takes about 1 hour... and bandwidth utilization is
> about 2mb... why is the difference in speed of getting data? What
> can be the contributor for this? I am using oracle 9i drivers for both
> servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
> 9.2.0.6 (2mb max). I dont think it can be the driver version. However
> I cant seem to find the 9.2.0.0 to rule out this possibility.
> Any help will be greatly appreciated.
> Regards, zomer.
>|||Both are at 100 Full Duplex. That is the Speed of the OAM router.
That OAM router is locked on 100 full also.
Everything else is the same on both machines... except the ora driver
version.

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.

backupset table info

My employer wants me to extract information for each nightly database backup
s
for each production server.
So I have created a DTS package that extracts this info into excel using the
following script:
use msdb
go
select database_name as dbname,
convert(char(20),backup_start_date,100) as start_time,
convert(char(20),backup_finish_date,100)
as end_time, backup_size from
backupset
where backup_start_date >
(SELECT MAX(backup_start_date) FROM backupset) - 1
order by database_name, backup_start_date
Which gives the following output:
dbname start_time end_time backup_size
db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
He now wants two additional columns in the excel sheet. One with yesterday's
backup_size and 1 w ago's backup size.
Can anyone tell me how to modify this sql script to be able to retieve this
additional info from the backupset table?
Thanks,
HillaireLook at sysbackuphistory system table.
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> My employer wants me to extract information for each nightly database
backups
> for each production server.
> So I have created a DTS package that extracts this info into excel using
the
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> DB2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with
yesterday's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve
this
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||Try,
select
a.database_name as dbname,
convert(char(20),a.backup_start_date,100) as start_time,
convert(char(20),a.backup_finish_date,100) as end_time,
a.backup_size,
b.backup_size
from
msdb.dbo.backupset as a
left join
msdb.dbo.backupset as b
on
a.database_name = b.database_name
and a.backup_start_date > (SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset) - 1
and b.backup_start_date = dateadd(w, -1, (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset) - 1)
order by
a.database_name,
a.backup_start_date
AMB
"gracie" wrote:

> My employer wants me to extract information for each nightly database back
ups
> for each production server.
> So I have created a DTS package that extracts this info into excel using t
he
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with yesterday
's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve thi
s
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||What I actually need to accomplish is a comparison on the same excel sheet
showing yesterday's backup size and 1 w ago's backup size, side by side s
o
that he can look for any large decrepancies in the sizes.
"Uri Dimant" wrote:

> Look at sysbackuphistory system table.
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> backups
> the
> yesterday's
> this
>
>