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