Accessing a DSN through a Sql job

I am trying to execute an SSIS package (which accesses a dbf file through an odbc connection) through a Sql job, but the package log reports an error of "Disk or netowrk error". When I execute the package in the IDE, the package executes fine. When I run the manifest on the DB server, I can execute the package with no errors. But, when I create the job, and try to execute the job, it fails. I thought at first that the user didn't have privileges to the directory that the file existed in, but that isn't the case.

Can anyone shed some light on how to accomplish what I am trying to accomplish? It seems like this would be a common use of SSIS, but I cannot seem to get it to work.

Thanks in advance for any assistance you can provide!

Craig

This seems to be security issue, as your package can execute fine within the IDE, but not through the SQL Agent. I'd check the SQL Agent user account, along with the proxy details on the job.

|||

Thank you for your reply, Deniz.

Do you know of any web sites that I can get more information on setting up the Sql Agent account permissions? I granted that user admin privileges, but still receive the same error.

Craig Browder

craigster1976@.msn.com

Access SQL to Transact SQL - Translator?

Friends,
I am new to SQL Server, and I access it mainly through
Access (ODBC DSN).
I need my queries to run faster, so I want to switch from
regular Access queries against linked SQL tables to faster
pass-through queries written in Transact SQL.
QUESTION: Does anyone know of an easy way to translate
Access SQL into Transact SQL - in other words, a way to
copy the Access SQL from SQL View, and paste it into a
translator?
Thanks in advance ...
"bill morgan" <willmorgan@.lisco.com> wrote in message
news:3f3d01c4a5bc$4dbaf7f0$a301280a@.phx.gbl...
> I am new to SQL Server, and I access it mainly through
> Access (ODBC DSN).
> I need my queries to run faster, so I want to switch from
> regular Access queries against linked SQL tables to faster
> pass-through queries written in Transact SQL.
If you want performance you may want to consider using SQL Server VIEWS and
Stored Procedures.

> QUESTION: Does anyone know of an easy way to translate
> Access SQL into Transact SQL - in other words, a way to
> copy the Access SQL from SQL View, and paste it into a
> translator?
There is no "easy" way to do this since the SQL syntax is different between
these 2 products.
On suggestion though, you could try running SQL Profiler and capture the SQL
Server SQL generated by an Access query. This you might be able to translate
into an SQL Server View or Stored Procedure.
Steve
|||Steve,
Thanks for your response. Actually, I have been capturing
the Access SQL, and generally it is simple to translate it
into Transact SQL. It gets sticky, though, when trying to
translate computed fields. For example, SQL Server does
not accept IIF() functions, so I have to come up with
alternative syntax and structure.
Thanks again ...[vbcol=seagreen]
>--Original Message--
>"bill morgan" <willmorgan@.lisco.com> wrote in message
>news:3f3d01c4a5bc$4dbaf7f0$a301280a@.phx.gbl...
from[vbcol=seagreen]
faster
>If you want performance you may want to consider using
SQL Server VIEWS and
>Stored Procedures.
>
>There is no "easy" way to do this since the SQL syntax is
different between
>these 2 products.
>On suggestion though, you could try running SQL Profiler
and capture the SQL
>Server SQL generated by an Access query. This you might
be able to translate
>into an SQL Server View or Stored Procedure.
>Steve
>
>.
>

Access denied thru ole db but not DSN

I have an asp page that at one time worked.
Now I get the below error msg. It only happens on my asp
page. I can run through access or vb and it connects fine.
It also works fine through a dsn. I have looked everywhere
to no avail. it is sql 2k sp 3.
any help is mucho appreciated
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNMPNTW]Access denied.
/macro/conn_test.asp, line 20
code:
<%
dim lca, sql, region
set lca = server.createobject
("adodb.connection")
lca.connectionstring
= " Provider=SQLoLEDB;Server=231v9;UID=LCAP;
Pwd=fxtool;Datab
ase=LCAOPS"
lca.connectiontimeout = 30
lca.Open
lca.close
%>Have you checked permissions on the registry keys? You could use regmon
from sysinternals.com to see what keys it accesses and see if it gets any
access denied messages.
Or maybe it can't find (or doesn't have security to see) the actual OLE DB
driver?
Will a UDL on the IIS machine, using that same driver and connection info,
connect to the SQL Server?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||This connection that is failing is using named pipes. Try adding this to
the connect string,
Network Library = dbmssocn. this will force it to use tcp/ip.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I am having the same problem Kevin is.
when I try to connect to my SQL Server (it is on a separate box from my
development workstation) through a simple windows application it works fine.
When I use the exact same code in an .aspx file, I get SQL Server does not
exist or access is denied.
I tried adding Network Library=dbmssocn, but when I did, even the windows
app stopped working.
Any suggetions would be appretiated.
thank you
Kirk Graves
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:fQ93b$d5DHA.2768@.cpmsftngxa07.phx.gbl...
> This connection that is failing is using named pipes. Try adding this to
> the connect string,
> Network Library = dbmssocn. this will force it to use tcp/ip.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>