SSIS Connection...

[Matthew Wells]

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
matthe...@firstbyte.net

[JAW]

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...@firstbyte.net>
wrote:

> matthew.we...@firstbyte.net

[Matthew Wells]

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" <will...@gmail.com> wrote in message
news:e98379ea-a278-476b...@u37g2000vbc.googlegroups.com...

[JAW]

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" <matthe...@firstbyte.net> wrote in message
news:7K8_m.126539$Wf2....@newsfe23.iad...

[Erland Sommarskog]

Matthew Wells (matthe...@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, esq...@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

[Matthew Wells]

All services were running under LocalSystem. I created a new user with
Domain Admin rights and have changed all services to use this account. I
know remote connections are allowed because I've created a .udl file and
connected under sa and my windows login. I'm still getting the same
message. I also didn't see anything in the configuration manager that would
help. I connected the SSIS service to SSMS and there's nothing there
either. Do you have any other suggestions?

"JAW" <will...@mindspring.com> wrote in message
news:6ZWdnSt25eDZuaTW...@earthlink.com...

[Erland Sommarskog]

Matthew Wells (matthe...@firstbyte.net) writes:
> All services were running under LocalSystem. I created a new user with
> Domain Admin rights and have changed all services to use this account.
> I know remote connections are allowed because I've created a .udl file
> and connected under sa and my windows login. I'm still getting the same
> message. I also didn't see anything in the configuration manager that
> would help. I connected the SSIS service to SSMS and there's nothing
> there either. Do you have any other suggestions?

You did restart the services, didn't you?`

And you did change it to a UNC path?

[Matthew Wells]

Follow up!!

I finally got it. The key seems to be that you have to open port 1434 for
SQL Server Browser. That's pretty much all I did. I didn't even open up
ports on the local machine firewall.

BTW, you can find Surface Area Configuration Manager in SSMS. Right click
the server and click Facets. SACM is in the drop down at the top. Doesn't
seem necessary since all the options can be located elsewhere.

Thank you all for your replies.

"Matthew Wells" <matthe...@firstbyte.net> wrote in message

news:sjp_m.12261$yM1....@newsfe11.iad...