Prev: query that doesn`t work - incorrect syntax near the keyword group
Next: Volpet Table Diff - Feedback Needed
From: Matthew Wells on 28 Dec 2009 12:50
Hello

I'm building an SSIS package from home to be deployed at an office. The
package is a simple Access to SQL Server migration. I was building the SSIS
package on a local machine which also housed the Access file. I decided to
open the SSIS package from my laptop. Both the laptop and the workstation I
was using use the same mapped share - F:\. The Access connection is OLE DB
/ Office 12. The SQL Server is an SQL Server Destination object. Now when
I open the SQL Destination, I get this warning at the bottom and I can't map
columns:

"The selected data source is on a remote computer. The bulk insert
operation can be executed only on the local computer."

When I run this package at the office, the Access file will not - and can
not - be on the local machine. Is there a way around this?

Thanks.

Matthew Wells
matthew.wells(a)firstbyte.net


From: JAW on 28 Dec 2009 14:43
Assume you are running 2005 you can check the Surface Area
Configuration Manager and make sure remote connections are being
allowed. That is the first thing I would look at.

On Dec 28, 12:50 pm, "Matthew Wells" <matthew.we...(a)firstbyte.net>
wrote:
> Hello
>
>     I'm building an SSIS package from home to be deployed at an office.  The
> package is a simple Access to SQL Server migration.  I was building the SSIS
> package on a local machine which also housed the Access file.  I decided to
> open the SSIS package from my laptop.  Both the laptop and the workstation I
> was using use the same mapped share - F:\.  The Access connection is OLE DB
> / Office 12.  The SQL Server is an SQL Server Destination object.  Now when
> I open the SQL Destination, I get this warning at the bottom and I can't map
> columns:
>
> "The selected data source is on a remote computer.  The bulk insert
> operation can be executed only on the local computer."
>
> When I run this package at the office, the Access file will not - and can
> not - be on the local machine.  Is there a way around this?
>
> Thanks.
>
> Matthew Wells
> matthew.we...(a)firstbyte.net

From: Matthew Wells on 28 Dec 2009 15:41
I'm using 2008 and did a little research and found that SACM is not
available anymore. I found some info on configuring other SQL engines, but
nothing for Access mdb files. Do you know where to look?

Thanks.

"JAW" <willjamu(a)gmail.com> wrote in message
news:e98379ea-a278-476b-b78a-58b8778aaedd(a)u37g2000vbc.googlegroups.com...
> Assume you are running 2005 you can check the Surface Area
> Configuration Manager and make sure remote connections are being
> allowed. That is the first thing I would look at.
>
> On Dec 28, 12:50 pm, "Matthew Wells" <matthew.we...(a)firstbyte.net>
> wrote:
>> Hello
>>
>> I'm building an SSIS package from home to be deployed at an office.
>> The
>> package is a simple Access to SQL Server migration. I was building the
>> SSIS
>> package on a local machine which also housed the Access file. I decided
>> to
>> open the SSIS package from my laptop. Both the laptop and the
>> workstation I
>> was using use the same mapped share - F:\. The Access connection is OLE
>> DB
>> / Office 12. The SQL Server is an SQL Server Destination object. Now
>> when
>> I open the SQL Destination, I get this warning at the bottom and I can't
>> map
>> columns:
>>
>> "The selected data source is on a remote computer. The bulk insert
>> operation can be executed only on the local computer."
>>
>> When I run this package at the office, the Access file will not - and can
>> not - be on the local machine. Is there a way around this?
>>
>> Thanks.
>>
>> Matthew Wells
>> matthew.we...(a)firstbyte.net
>
From: JAW on 28 Dec 2009 16:33
In 2008 the account (SQL Server or Windows) if it has been granted DBACCESS
it should have what it needs.

Also, I think the access drivers come from MDAC. You should make sure that
is on your computer as well.

I have not experienced this issue in 2008.


"Matthew Wells" <matthew.wells(a)firstbyte.net> wrote in message
news:7K8_m.126539$Wf2.67624(a)newsfe23.iad...
> I'm using 2008 and did a little research and found that SACM is not
> available anymore. I found some info on configuring other SQL engines,
> but nothing for Access mdb files. Do you know where to look?
>
> Thanks.
>
> "JAW" <willjamu(a)gmail.com> wrote in message
> news:e98379ea-a278-476b-b78a-58b8778aaedd(a)u37g2000vbc.googlegroups.com...
>> Assume you are running 2005 you can check the Surface Area
>> Configuration Manager and make sure remote connections are being
>> allowed. That is the first thing I would look at.
>>
>> On Dec 28, 12:50 pm, "Matthew Wells" <matthew.we...(a)firstbyte.net>
>> wrote:
>>> Hello
>>>
>>> I'm building an SSIS package from home to be deployed at an office.
>>> The
>>> package is a simple Access to SQL Server migration. I was building the
>>> SSIS
>>> package on a local machine which also housed the Access file. I decided
>>> to
>>> open the SSIS package from my laptop. Both the laptop and the
>>> workstation I
>>> was using use the same mapped share - F:\. The Access connection is OLE
>>> DB
>>> / Office 12. The SQL Server is an SQL Server Destination object. Now
>>> when
>>> I open the SQL Destination, I get this warning at the bottom and I can't
>>> map
>>> columns:
>>>
>>> "The selected data source is on a remote computer. The bulk insert
>>> operation can be executed only on the local computer."
>>>
>>> When I run this package at the office, the Access file will not - and
>>> can
>>> not - be on the local machine. Is there a way around this?
>>>
>>> Thanks.
>>>
>>> Matthew Wells
>>> matthew.we...(a)firstbyte.net
>>

From: Erland Sommarskog on 28 Dec 2009 17:36
Matthew Wells (matthew.wells(a)firstbyte.net) writes:
> I'm building an SSIS package from home to be deployed at an office.
> The package is a simple Access to SQL Server migration. I was building
> the SSIS package on a local machine which also housed the Access file.
> I decided to open the SSIS package from my laptop. Both the laptop and
> the workstation I was using use the same mapped share - F:\. The Access
> connection is OLE DB / Office 12. The SQL Server is an SQL Server
> Destination object. Now when I open the SQL Destination, I get this
> warning at the bottom and I can't map columns:
>
> "The selected data source is on a remote computer. The bulk insert
> operation can be executed only on the local computer."

Disclaimer: I've never worked with SSIS, so may be off the target entirely.

But it seems to me that you have set up the SSIS jon to use BULK INSERT,
and BULK INSERT cannot read from drive F, because for the SQL Service
there is no drive F. They only exist for you as a user. Instead you
should specify a UNC path, that is \\machine\share\etc. Furthermore,
you need to make sure that SQL Server runs under a domain account
with access to that drive. Don't use LocalSystem or somesuch.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: query that doesn`t work - incorrect syntax near the keyword group
Next: Volpet Table Diff - Feedback Needed