DTS Package Timeout Error

I have two simple dts pacakges , DTS_Main and DTS_Sub.
DTS_Sub being called from DTS_Main.
When DTS_Main calls DTS_Sub getting the following timeout error while
calling the package not while executing.
Is there any settings I can set to increase the time out setting when
calling sub package.Both dts packages
exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
processor with 12 GB Ram.
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Timeout expired Error source: Microsoft OLE DB Provider
for SQL Server
Help file:
Help context: 0.
Process Exit Code 1. The step failed.
Please advise.
Hello DK,
The error message seems to be more to do with connecting to your SQL
Server. First off are all the DTS packages on the same server?
If all DTS packages are on the same server try the following:
I would try and run the sub package independently of the main package.
Also I would test all the database connections in the DTS package.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Ran the main and sub packages individually they ran fine.
Both the packages are in the same server.I have tested all the connections
inside the packages.
I have changed the connection timeout for all connections to 600000
My question is while main package opening the sub package it's taking more
time, where I can change this
time out setting for opening dts packages..
<Myles.Matheson@.gmail.com> wrote in message
news:1130131342.421163.88560@.g49g2000cwa.googlegro ups.com...
> Hello DK,
> The error message seems to be more to do with connecting to your SQL
> Server. First off are all the DTS packages on the same server?
> If all DTS packages are on the same server try the following:
> I would try and run the sub package independently of the main package.
> Also I would test all the database connections in the DTS package.
> Hope this helps,
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
|||Hello DK,
This could be a permissions problem. Did you manually run the sub
package or scheduled it?
If you manually ran the package your user context would have been used.
This may include all the aliases for databases connections. The timeout
error could have been trying to connect on a different port requiring
an alias.
Are you using server aliases for you db connections?
Try scheduling the sub package on its own to see if you get the same
error. Or logon under the SQL agent account and the sub package.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||> Ran the main and sub packages individually they ran fine.
If the main package ran fine, does that mean it runs OK from the
designer, but not as a scheduled job? If this is the case the issue is
probably that the account that SQL Agent is running under does not have
permission to run the packages.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||Since the DTS packages are on the same server why not create a job and have
the pacakges executed as separate steps?
Thomas
"Myles.Matheson@.gmail.com" wrote:

> Hello DK,
> The error message seems to be more to do with connecting to your SQL
> Server. First off are all the DTS packages on the same server?
> If all DTS packages are on the same server try the following:
> I would try and run the sub package independently of the main package.
> Also I would test all the database connections in the DTS package.
> Hope this helps,
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
|||i have many dts packages, so i want to include them in one package with
different conditions.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:56580403-46BA-43BD-A334-8C96CF887B7F@.microsoft.com...
> Since the DTS packages are on the same server why not create a job and
have[vbcol=seagreen]
> the pacakges executed as separate steps?
> --
> Thomas
>
> "Myles.Matheson@.gmail.com" wrote:
sqlsql

DTS Package Timeout Error

I have two simple dts pacakges , DTS_Main and DTS_Sub.
DTS_Sub being called from DTS_Main.
When DTS_Main calls DTS_Sub getting the following timeout error while
calling the package not while executing.
Is there any settings I can set to increase the time out setting when
calling sub package.Both dts packages
exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
processor with 12 GB Ram.
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Timeout expired Error source: Microsoft OLE DB Provider
for SQL Server
Help file:
Help context: 0.
Process Exit Code 1. The step failed.
Please advise.Hello DK,
The error message seems to be more to do with connecting to your SQL
Server. First off are all the DTS packages on the same server?
If all DTS packages are on the same server try the following:
I would try and run the sub package independently of the main package.
Also I would test all the database connections in the DTS package.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||Ran the main and sub packages individually they ran fine.
Both the packages are in the same server.I have tested all the connections
inside the packages.
I have changed the connection timeout for all connections to 600000
My question is while main package opening the sub package it's taking more
time, where I can change this
time out setting for opening dts packages..
<Myles.Matheson@.gmail.com> wrote in message
news:1130131342.421163.88560@.g49g2000cwa.googlegroups.com...
> Hello DK,
> The error message seems to be more to do with connecting to your SQL
> Server. First off are all the DTS packages on the same server?
> If all DTS packages are on the same server try the following:
> I would try and run the sub package independently of the main package.
> Also I would test all the database connections in the DTS package.
> Hope this helps,
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>|||Hello DK,
This could be a permissions problem. Did you manually run the sub
package or scheduled it?
If you manually ran the package your user context would have been used.
This may include all the aliases for databases connections. The timeout
error could have been trying to connect on a different port requiring
an alias.
Are you using server aliases for you db connections?
Try scheduling the sub package on its own to see if you get the same
error. Or logon under the SQL agent account and the sub package.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||> Ran the main and sub packages individually they ran fine.
If the main package ran fine, does that mean it runs OK from the
designer, but not as a scheduled job? If this is the case the issue is
probably that the account that SQL Agent is running under does not have
permission to run the packages.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||Since the DTS packages are on the same server why not create a job and have
the pacakges executed as separate steps?
--
Thomas
"Myles.Matheson@.gmail.com" wrote:

> Hello DK,
> The error message seems to be more to do with connecting to your SQL
> Server. First off are all the DTS packages on the same server?
> If all DTS packages are on the same server try the following:
> I would try and run the sub package independently of the main package.
> Also I would test all the database connections in the DTS package.
> Hope this helps,
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>|||i have many dts packages, so i want to include them in one package with
different conditions.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:56580403-46BA-43BD-A334-8C96CF887B7F@.microsoft.com...
> Since the DTS packages are on the same server why not create a job and
have[vbcol=seagreen]
> the pacakges executed as separate steps?
> --
> Thomas
>
> "Myles.Matheson@.gmail.com" wrote:
>

DTS Package Timeout error

I have two simple dts pacakges , DTS_Main and DTS_Sub.
DTS_Sub being called from DTS_Main.
When DTS_Main calls DTS_Sub getting the following timeout error while
calling the package not while executing.
Is there any settings I can set to increase the time out setting when
calling sub package.Both dts packages
exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
processor with 12 GB Ram.
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Timeout expired Error source: Microsoft OLE DB Provider
for SQL Server
Help file:
Help context: 0.
Process Exit Code 1. The step failed.
Please advise.Hi
I have never seen this and don't know where you can set the timeout you may
want to post to microsoft.public.sqlserver.dts.
You don't say which version of SQL Server you are using, what version of
MDAC is installed and if the sub-package is a file or not.
Does the package work if run manually?
Does SQL profiler show anything?
John
"DKRReddy" wrote:

> I have two simple dts pacakges , DTS_Main and DTS_Sub.
> DTS_Sub being called from DTS_Main.
> When DTS_Main calls DTS_Sub getting the following timeout error while
> calling the package not while executing.
> Is there any settings I can set to increase the time out setting when
> calling sub package.Both dts packages
> exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
> processor with 12 GB Ram.
> Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: Timeout expired Error source: Microsoft OLE DB Provid
er
> for SQL Server
> Help file:
> Help context: 0.
> Process Exit Code 1. The step failed.
>
> Please advise.
>
>

DTS Package Timeout error

I have two simple dts pacakges , DTS_Main and DTS_Sub.
DTS_Sub being called from DTS_Main.
When DTS_Main calls DTS_Sub getting the following timeout error while
calling the package not while executing.
Is there any settings I can set to increase the time out setting when
calling sub package.Both dts packages
exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
processor with 12 GB Ram.
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Timeout expired Error source: Microsoft OLE DB Provider
for SQL Server
Help file:
Help context: 0.
Process Exit Code 1. The step failed.
Please advise.
Hi
I have never seen this and don't know where you can set the timeout you may
want to post to microsoft.public.sqlserver.dts.
You don't say which version of SQL Server you are using, what version of
MDAC is installed and if the sub-package is a file or not.
Does the package work if run manually?
Does SQL profiler show anything?
John
"DKRReddy" wrote:

> I have two simple dts pacakges , DTS_Main and DTS_Sub.
> DTS_Sub being called from DTS_Main.
> When DTS_Main calls DTS_Sub getting the following timeout error while
> calling the package not while executing.
> Is there any settings I can set to increase the time out setting when
> calling sub package.Both dts packages
> exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
> processor with 12 GB Ram.
> Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: Timeout expired Error source: Microsoft OLE DB Provider
> for SQL Server
> Help file:
> Help context: 0.
> Process Exit Code 1. The step failed.
>
> Please advise.
>
>

DTS Package Timeout error

I have two simple dts pacakges , DTS_Main and DTS_Sub.
DTS_Sub being called from DTS_Main.
When DTS_Main calls DTS_Sub getting the following timeout error while
calling the package not while executing.
Is there any settings I can set to increase the time out setting when
calling sub package.Both dts packages
exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
processor with 12 GB Ram.
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Timeout expired Error source: Microsoft OLE DB Provider
for SQL Server
Help file:
Help context: 0.
Process Exit Code 1. The step failed.
Please advise.Hi
I have never seen this and don't know where you can set the timeout you may
want to post to microsoft.public.sqlserver.dts.
You don't say which version of SQL Server you are using, what version of
MDAC is installed and if the sub-package is a file or not.
Does the package work if run manually?
Does SQL profiler show anything?
John
"DKRReddy" wrote:
> I have two simple dts pacakges , DTS_Main and DTS_Sub.
> DTS_Sub being called from DTS_Main.
> When DTS_Main calls DTS_Sub getting the following timeout error while
> calling the package not while executing.
> Is there any settings I can set to increase the time out setting when
> calling sub package.Both dts packages
> exists in Same SQL server (SQL 2000 SP4 + Windows 2003), the server is 8
> processor with 12 GB Ram.
> Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: Timeout expired Error source: Microsoft OLE DB Provider
> for SQL Server
> Help file:
> Help context: 0.
> Process Exit Code 1. The step failed.
>
> Please advise.
>
>

DTS Package Problem

I have a stored procedure that calls a DTS package using xp_cmdshell dtsrun. The problem is strange, I can execute the stored procedure from Query Analyzer and it works fine. I call the stored procedure from inside of an ASP.NET application and it seems to execute but doesn't. The user account for the ASP.NET application has rights to execute the stored procedure and I have run a trace and so see any login failures. I'm stumped, any clues??

neel

xp_cmdshell can run an enterprise but it is a SQL Server Agent dependent service, you have to install SQL Server Agent with a service account to run all the dependent services. Hope this helps|||

Does the SQL Server agent have to be on the client computer or just the computer with SQL Server on it. I would think since I'm just sending the stored procedure name of the stored procedure on SQL Server that that server would be the only place I need the SQL Server Agent?

Thanks

Neel

|||The SQL Server Agent in the computer executing xp_cmdshell needs a service account because you can schedule xp_cmdshell with a job to transfer data from main frame to another SQL Server in the same network and it will run it. SQL Server Agent needs a service account if your application will use any of its dependent services like DTS Automation, Replication and jobs. Microsoft keep this from developers because there is a lot to know in development. Hope this help|||

Thanks it is working now. I also had to go in and setup a proxy account in the SQL Server Agent that had sysadmin rights because the users of the program and the program user account do not have sysadmin rights.

Thanks Again

Neel

DTS Migration

I try to migrate DTS package to SSIS.

Within one DTS package AAA, it calls another DTS package BBB with inner variable.

I successfully migrate the BBB DTS package.

For my AAA package, I replace the DTS_Execute_Task_2000 with Execute Package task. But how do we pass the inner variable parameter ?

There is a Execute DTS2000 Package Task that allows you to do this.

-Jamie

|||Is there a way not to use DTS2000 Package ?|||

You could call dtsrun.exe from the Execute Process Task. Not sure why you'd want to tho.

-Jamie

DTS job

Hi,

If my one .dtsx jobs calls another .dtsx jobs, how this situation should be taken care?Please help me out on this.

Regards

Nitu

You should use the ExecutePackage task. See http://msdn2.microsoft.com/en-us/library/ms137609(SQL.90).aspx

Donald Farmer

DTS is crazy! or maybe its me?

Hey all,
I need to import an excel file into a SQL table. Users are able to modify and save the excel file. I have a stored procedure that calls a structured storage file which does the import. Both the excel file and the structured storage file reside in the same location. The first time that the stored procedure fires the DTS it works fine, but if the data in the excel file is overwirtten by a user it appears that the import does not function.
Am I missing something here?
Here is the SPROC:
CREATE PROCEDURE [dbo].[importEmployees] AS

DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, 'C:\importEmployees.dts', ''
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

GO

Ok, so it was me... I had the datatype wrong on the excel file which was set to numeric and I put in text.