RE: Unable to run DTS package.

#1

Hi,

I do not understand "When the package runs from developer's credential job
executes fine." How do you run job from developer's credential? Can you
configure SQL Server Agent service to run under developer's credential?

Also I do not understand "ODBC connection seems to have no option for using
UNC path".

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

Quote:

Content-Class: urn:content-classes:message
From: "Dipti" <diptir (AT) atlantia (DOT) com
Sender: "Dipti" <diptir (AT) atlantia (DOT) com
Subject: Unable to run DTS package.
Date: Thu, 14 Aug 2003 08:34:52 -0700
Lines: 34
Message-ID: <118d01c36279$9acae960$a401280a (AT) phx (DOT) gbl
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcNieZrKO0VlVqOtQ2GGFzYQ9/o18A==
Newsgroups: microsoft.public.sqlserver.dts
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:37346
NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
X-Tomcat-NG: microsoft.public.sqlserver.dts

We are trying to run a DTS package on SQL server 2000.
This package is to connect to an accounting server which
hosts Timberline software. Extract data from accounting
server and put it into SQL database. We created an ODBC
connector (system DSN) for this connection. When the
package runs from developer's credential job executes fine
but when we schedule the job to run using SQL Server
Agent, job fails. SA is a domain account with membership
to domain admin/enetrprise admin. Domain Admin has full
access to Accounting server.

We reviewed the Q269074 and webcast dated October 23,2001.
Seems like we are doing everything right as per
documentation. Only thing I am little concerned is may be
SA can not interpret the mapped drive (ODBC connection
seems to have no option for using UNC path), how do we get
around to this?

I am pasting the message generated by DTS run:

Step Error Source: Microsoft OLE DB Provider for ODBC
Drivers
Step Error Description:[Timberline][Timberline ODBC Driver]
Unable to open table: MASTER_APM_INVOICE. (Microsoft OLE
DB Provider for ODBC Drivers (80004005): [Timberline]
[Timberline ODBC Driver][File Library]Disk I/O error.)
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Looking at this error message, seems like connection is
made to server but tables can not be accessed. Any input
will be appreciated.

#2

I posted the reply to news group last week, seems like it
did not show up yet. Anyways, by "developer' credential",
I meant when the developer interactively runs the package
with her domain log on credential from her desk top via
terminal service to SQL server, that job seems to run
fine. When the job is run interactively or scheduled on
SQL server with domain adminstrator credential job runs
fine. But job does not run when we schedule it with "SA"
which is sql service account on the domain and it has
membership to domian administrator group.

Now for "ODBC connection seems to have no UNC path" , I
meant is when I create a system DSN and configure it, I am
suppose to define a path for this DSN, only option there
is to choose from the drop down which is basically the
mapped drive. I do not see any way to use UNC.

We are still dealing with this issue. So, any input will
be appreciated. Thanks.

Quote:

-----Original Message-----
Hi,

I do not understand "When the package runs from
developer's credential job
executes fine." How do you run job from developer's
credential? Can you
configure SQL Server Agent service to run under
developer's credential?

Also I do not understand "ODBC connection seems to have
no option for using
UNC path".

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and
confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Dipti" <diptir (AT) atlantia (DOT) com
| Sender: "Dipti" <diptir (AT) atlantia (DOT) com
| Subject: Unable to run DTS package.
| Date: Thu, 14 Aug 2003 08:34:52 -0700
| Lines: 34
| Message-ID: <118d01c36279$9acae960$a401280a (AT) phx (DOT) gbl
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNieZrKO0VlVqOtQ2GGFzYQ9/o18A==
| Newsgroups: microsoft.public.sqlserver.dts
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.dts:37346
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| We are trying to run a DTS package on SQL server 2000.
| This package is to connect to an accounting server
which
| hosts Timberline software. Extract data from accounting
| server and put it into SQL database. We created an
ODBC
| connector (system DSN) for this connection. When the
| package runs from developer's credential job executes
fine
| but when we schedule the job to run using SQL Server
| Agent, job fails. SA is a domain account with
membership
| to domain admin/enetrprise admin. Domain Admin has full
| access to Accounting server.
|
| We reviewed the Q269074 and webcast dated October
23,2001.
| Seems like we are doing everything right as per
| documentation. Only thing I am little concerned is may
be
| SA can not interpret the mapped drive (ODBC connection
| seems to have no option for using UNC path), how do we
get
| around to this?
|
| I am pasting the message generated by DTS run:
|
| Step Error Source: Microsoft OLE DB Provider for ODBC
| Drivers
| Step Error Description:[Timberline][Timberline ODBC
Driver]
| Unable to open table: MASTER_APM_INVOICE. (Microsoft
OLE
| DB Provider for ODBC Drivers (80004005): [Timberline]
| [Timberline ODBC Driver][File Library]Disk I/O error.)
| Step Error code: 80004005
| Step Error Help File:
| Step Error Help Context ID:0
|
| Looking at this error message, seems like connection is
| made to server but tables can not be accessed. Any
input
| will be appreciated.
|
|

.

#3

Hi,

I am afraid that I do not understand you fully. You said "When the job is
run interactively or scheduled on SQL server with domain adminstrator
credential job runs fine." Please check my understanding is correct. I
assume that the SQL Server Agent job runs fine under the following
situations:
1. When you right-click a job in Enterprise Manager and select Start Job.
2. Configure SQL Server Agent to run under domain adminstrator account.

If my understanding is incorrect, please let me know.

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

Quote:

Content-Class: urn:content-classes:message
From: "Dipti" <diptir (AT) atlantia (DOT) com
Sender: "Dipti" <diptir (AT) atlantia (DOT) com
References: <118d01c36279$9acae960$a401280a (AT) phx (DOT) gbl
FyxDNWwYDHA.2080 (AT) cpmsftngxa06 (DOT) phx.gbl
Subject: RE: Unable to run DTS package.
Date: Mon, 18 Aug 2003 06:59:26 -0700
Lines: 110
Message-ID: <085401c36590$ef8b5dd0$a601280a (AT) phx (DOT) gbl
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Thread-Index: AcNlkO+Lq6ITfyQeTQejcJVzc8B34w==
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Newsgroups: microsoft.public.sqlserver.dts
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:37473
NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
X-Tomcat-NG: microsoft.public.sqlserver.dts

I posted the reply to news group last week, seems like it
did not show up yet. Anyways, by "developer' credential",
I meant when the developer interactively runs the package
with her domain log on credential from her desk top via
terminal service to SQL server, that job seems to run
fine. When the job is run interactively or scheduled on
SQL server with domain adminstrator credential job runs
fine. But job does not run when we schedule it with "SA"
which is sql service account on the domain and it has
membership to domian administrator group.

Now for "ODBC connection seems to have no UNC path" , I
meant is when I create a system DSN and configure it, I am
suppose to define a path for this DSN, only option there
is to choose from the drop down which is basically the
mapped drive. I do not see any way to use UNC.

We are still dealing with this issue. So, any input will
be appreciated. Thanks.
-----Original Message-----
Hi,

I do not understand "When the package runs from
developer's credential job
executes fine." How do you run job from developer's
credential? Can you
configure SQL Server Agent service to run under
developer's credential?

Also I do not understand "ODBC connection seems to have
no option for using
UNC path".

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and
confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Dipti" <diptir (AT) atlantia (DOT) com
| Sender: "Dipti" <diptir (AT) atlantia (DOT) com
| Subject: Unable to run DTS package.
| Date: Thu, 14 Aug 2003 08:34:52 -0700
| Lines: 34
| Message-ID: <118d01c36279$9acae960$a401280a (AT) phx (DOT) gbl
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNieZrKO0VlVqOtQ2GGFzYQ9/o18A==
| Newsgroups: microsoft.public.sqlserver.dts
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.dts:37346
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| We are trying to run a DTS package on SQL server 2000.
| This package is to connect to an accounting server
which
| hosts Timberline software. Extract data from accounting
| server and put it into SQL database. We created an
ODBC
| connector (system DSN) for this connection. When the
| package runs from developer's credential job executes
fine
| but when we schedule the job to run using SQL Server
| Agent, job fails. SA is a domain account with
membership
| to domain admin/enetrprise admin. Domain Admin has full
| access to Accounting server.
|
| We reviewed the Q269074 and webcast dated October
23,2001.
| Seems like we are doing everything right as per
| documentation. Only thing I am little concerned is may
be
| SA can not interpret the mapped drive (ODBC connection
| seems to have no option for using UNC path), how do we
get
| around to this?
|
| I am pasting the message generated by DTS run:
|
| Step Error Source: Microsoft OLE DB Provider for ODBC
| Drivers
| Step Error Description:[Timberline][Timberline ODBC
Driver]
| Unable to open table: MASTER_APM_INVOICE. (Microsoft
OLE
| DB Provider for ODBC Drivers (80004005): [Timberline]
| [Timberline ODBC Driver][File Library]Disk I/O error.)
| Step Error code: 80004005
| Step Error Help File:
| Step Error Help Context ID:0
|
| Looking at this error message, seems like connection is
| made to server but tables can not be accessed. Any
input
| will be appreciated.
|
|

.

#4

Sorry, I misinformed you. Here is the actual situation:

When we schedule the job, no matter who the job owner is,
job fails. If you run interactively as administrator
and/or with domain sa account, package runs correctly. Sql
server agent runs with domain sa account.

Quote:

#5

Sorry, I misinformed you. Here is the actual situation:

When we schedule the job, no matter who the job owner is,
job fails. If you run interactively as administrator
and/or with domain sa account, package runs correctly. Sql
server agent runs with domain sa account.

Quote:

#6

Dipti,

I have the same issue with Timberline. The strange thing
is that I had this working back in February, but after
installing Biztalk on the same server, it stopped working.

I've restaged the server, and completed the steps that
Bill listed, and still have the same issue. I don't know
how much help I can be, but if you would like additional
sounding board please contact me.

Dave

Quote:

#7

Just an update. I have been able to write a file to the
mapped drive with the same package that fails with the
ODBC connection. This should be using the same security
context for execution.

Hope this helps. I'm starting to run ODBC traces now.

Quote:

#8

Hi,

From the information provided by David, it seems that the problem is with
Timberline, as we can write to a UNC folder in the package when it is
scheduled. Please post the information requested in my previous mail, as I
am not very familiar with the Timberline software. It might help with the
issue.

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

Quote:

Content-Class: urn:content-classes:message
From: "Dipti" <diptir (AT) atlantia (DOT) com
Sender: "Dipti" <diptir (AT) atlantia (DOT) com
References: <118d01c36279$9acae960$a401280a (AT) phx (DOT) gbl
FyxDNWwYDHA.2080 (AT) cpmsftngxa06 (DOT) phx.gbl

<085401c36590$ef8b5dd0$a601280a (AT) phx (DOT) gbl>
<eTSfAdhZDHA.2108 (AT) cpmsftngxa06 (DOT) phx.gbl>

Quote:

#9

I have more to contribute. After speaking with
Timberline, they relayed that their ODBC driver had a
conflict with Service Pack 4. In particular, MS Access
was able to read a list of tables available for linking /
importing, but would fail when attmempting to read data.

I restored SVC 2, and this error was eliminated.

Also, I can sucessfully execute my DTS package - the one
that writes the text file - from the cmd prompt with the
following syntax:

dtsrun '/Sserver /Nstagetimberline /E'. The Windows
scheduler can also execute this command, but only when I
am logged into the machine with the proper account.

I hope this helps.

The trusted connection works.

Quote:

#10

Have a look at this

http://support.microsoft.com/?kbid=269074

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"David Robbins" <drobbins (AT) taubman (DOT) com> wrote

Quote: