DTS Scheduled Job

I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.

DTS permissions?

i posted this in programming but nobody answered so I thought I might try he
re aswell.
Can anyone help us with the following...
we've got lots of DTS packages stored on SQL server, however we have noticed
that it takes the login credentials for what to save the DTS package owner
as from windows, and not from the SQL server login that you type in to save
it. EVEN though we have got
the SQL server installation set to not allow trusted connections.
What we are trying to do is get it so we can save each other's DTS packages
without being members of the sysadmin role. Is there anyway we can do it?
The dba (rightly) doesn't want to give us all sa rights, and we can't switch
over to windows authentication until we get our own domain (which is months
off), as otherwise we would have to give the IT department's domain adminis
trators access to our serve
rs, which we don't want to do.
Any other way round it?
The dba's just come up with some 'undocumented stored procedure' that might
enable us to change things in some way but he's going to let me have a look
after lunch, but has anybody else experienced this problem?BOL refers:
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the
DTS Run utility, or from the command prompt executes under the security cont
ext of the user who is currently logged in. However, a package scheduled for
execution runs under the s
ecurity context of the SQL Server Agent job that runs the package. The owner
of that job may or may not be the same as the user currently logged in.
For packages created under a Microsoft Windows NT? 4.0 or Microsoft Windows
? 2000 account, the job runs under the security context of the account that
started SQL Server Agent.
If the job is owned by a login belonging to the sysadmin fixed server role,
the security context of the package defaults to the account used to start th
e local SQL Server Agent. If the server is registered using Windows Authenti
cation, the owner of the jo
b is the account of the SQL Server Agent. If the server is registered using
SQL Server Authentication, the owner of the job is that SQL Server login.
If the job is owned by a login that is not a member of the sysadmin fixed se
rver role, the package runs under the context of the job step proxy account,
with the rights and permissions of that account.
--
Satya SKJ
"Beeeeeeeeeeeeves" wrote:

> i posted this in programming but nobody answered so I thought I might try
here aswell.
> Can anyone help us with the following...
> we've got lots of DTS packages stored on SQL server, however we have noticed that
it takes the login credentials for what to save the DTS package owner as from window
s, and not from the SQL server login that you type in to save it. EVEN though we hav
e g
ot the SQL server installation set to not allow trusted connections.
> What we are trying to do is get it so we can save each other's DTS package
s without being members of the sysadmin role. Is there anyway we can do it?
> The dba (rightly) doesn't want to give us all sa rights, and we can't switch over
to windows authentication until we get our own domain (which is months off), as othe
rwise we would have to give the IT department's domain administrators access to our
ser
vers, which we don't want to do.
> Any other way round it?
> The dba's just come up with some 'undocumented stored procedure' that migh
t enable us to change things in some way but he's going to let me have a loo
k after lunch, but has anybody else experienced this problem?
>|||Not helpful, please don't just quote BOL.
Re-read the question and re-consider whether your answer comes even vaguely
close to answering it, in case you didn't work it out, the answer is no.
The question was HOW CAN WE SAVE EACH OTHER'S PACKAGES. I also clearly state
d that the server is NOT windows authentication, and the users that the prob
lem affects are NOT members of the sysadmin role. Yet you chose to rabble on
(or copy and paste text th
at rabbles on) about "if you are using windows authentication" and "if you a
re in the sysadmin role".
The answer seems to be to save it in metadata services rather than local pac
kages.|||"When saving a local package Enterprise Manager calls sp_add_dtspackage
found in the msdb database. If you are not the owner and not a member of the
sysadmin role an error will be returned. If you fail this check the error
"Only the owner of DTS Package 'MyPackage' or a member of the sysadmin role
may create new versions of it." will be returned. A similar check is
contained within sp_drop_dtspackage which is used when deleting a package.
If multiple non-sysadmin developers all need to work on the same package
this limitation can cause major problems. One totally unsupported workaround
would be to remove the checks from the stored procedure. I have implemented
this successfully in development environments where teams all need to work
with the same packages, without any problems. "
The undocumented proc will sp_reassign_dtspackageowner, but that is no use
in a dev shop, as it bneeds running each time a package has been saved and a
new user wants access.
Package Ownership Issues
(http://www.sqldts.com/default.aspx?212)
Darren Green
http://www.sqldts.com
"Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
message news:65E2ED1D-0810-4044-B8EE-342171657FB8@.microsoft.com...
> Not helpful, please don't just quote BOL.
> Re-read the question and re-consider whether your answer comes even
vaguely close to answering it, in case you didn't work it out, the answer is
no.
> The question was HOW CAN WE SAVE EACH OTHER'S PACKAGES. I also clearly
stated that the server is NOT windows authentication, and the users that the
problem affects are NOT members of the sysadmin role. Yet you chose to
rabble on (or copy and paste text that rabbles on) about "if you are using
windows authentication" and "if you are in the sysadmin role".
> The answer seems to be to save it in metadata services rather than local
packages.
>|||Ah ... does it! cheers. I'll check that out as a potential loophole as all u
s devs have been made dbo of msdb.
Cheers
"Darren Green" wrote:

> "When saving a local package Enterprise Manager calls sp_add_dtspackage
> found in the msdb database. If you are not the owner and not a member of t
he
> sysadmin role an error will be returned. If you fail this check the error
> "Only the owner of DTS Package 'MyPackage' or a member of the sysadmin rol
e
> may create new versions of it." will be returned. A similar check is
> contained within sp_drop_dtspackage which is used when deleting a package.
> If multiple non-sysadmin developers all need to work on the same package
> this limitation can cause major problems. One totally unsupported workarou
nd
> would be to remove the checks from the stored procedure. I have implemente
d
> this successfully in development environments where teams all need to work
> with the same packages, without any problems. "
> The undocumented proc will sp_reassign_dtspackageowner, but that is no use
> in a dev shop, as it bneeds running each time a package has been saved and
a
> new user wants access.
> Package Ownership Issues
> (http://www.sqldts.com/default.aspx?212)
>
> --
> Darren Green
> http://www.sqldts.com
> "Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
> message news:65E2ED1D-0810-4044-B8EE-342171657FB8@.microsoft.com...
> vaguely close to answering it, in case you didn't work it out, the answer
is
> no.
> stated that the server is NOT windows authentication, and the users that t
he
> problem affects are NOT members of the sysadmin role. Yet you chose to
> rabble on (or copy and paste text that rabbles on) about "if you are using
> windows authentication" and "if you are in the sysadmin role".
> packages.
>
>

DTS Packages are not working as job

Hello,

I scheduled a dts package and it created automatically a job but the job fails with following error :
(reason: The system cannot find the file specified).

dts package is connecting the databases which exist on the server and I created this dts package on the same server as user sa.

I don't use a file as a data source or for something else in the package.

Server is SQL 2000 with SP3 and operating system is win2k.

I hope there is someone who has an idea...

elifElif:

What EXACTLY is you package doing? In other words, are you using ActiveX + VBS to create and Run the job?

RobbieD|||My package is copying a database into another one and changing some characters in some fields. I added a vb code to transformations screen for this.
The package works fine when executed. Problem is scheduling. I want to execute it at night because the people are working one of the databases which dts uses.

If I can not schedule this I have to schedule my own night or weekend. This is ridiculous.|||...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?|||Originally posted by robbied111
...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?

It worked and created the job. The problem is the "job". That does not work.|||I recall our DBA having the same issue:

I believe that the job may need to be scheduled directly on the server with the logged in user having admin priviledges on the server. It had less to do with SQL Server but was an effect of Windows 2000.

I may be offbase but that is what he found.

Originally posted by eguven
It worked and created the job. The problem is the "job". That does not work.|||The weather was stormy on friday, so we did not work.
Thanks for responses.
I'm working directly on the server and I'm user 'sa'.
I also prepared and scheduled the dts package on the server.
And this job does not work.
I have read something on the msdn related the user priviliges problem with this issue. That's why I did everything on the server as they said.
There is something else in my problem. I don't really understand.|||Dear friends,

I solved by myself this crazy problem. I wanted to share solution with you. When I scheduled the dts package, a job was created automatically. But it uses DTSRUN utility to run the package. This utility can only be run in the "c:\Program Files\Microsoft SQL Server\80\Tools\Binn " directory. I don't know why... But there is no path information in the job definition. That's why I recevied "File not found" message. I added this path to job and it worked. No need to be smart when working with a microft. We must just think simple.

bye

elif|||You might have removed it from the path variable while making some config settings ... otherwise it is automatically added when you install sql server .. You can view and set the path variables in System properties .. Advanced Tab ... Environment Variables ... No need to blame MS for your own fault. :)

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 failing with Object required WScript

I have the following error when I try to execute a DTS package.
All the package is doing, is a bit of WScript to map a drive (need it for other packages), so:

[code]

Function Main()
Dim WshNetwork
Set WshNetwork = WScript.CreateObject("WScript.Network")
WshNetwork.RemoveNetworkDrive "S:"
WshNetwork.MapNetworkDrive "S:", "\\myserver\folder1\folder2\folder3\folder4"
Main = DTSTaskExecResult_Success
End Function

[/code]

If I copy it out to a .vbs file and execute it logged in as the SQL Agent profile, it works. Execute the package under a job, or just execute it, it fails with the above error.
Any ideas?

This is a SSIS forum, not DTS! Try microsoft.public.sqlserver.dts, you're more likely to get help there.

-Jamie

|||WScript object is specific to wscript.exe host application (that runs .VBS files), it is not part of VBScript language, so you can't use it in DTS scripts.
I think just CreateObject (without "wscript.") should work as well, but I don't know VBScript. You may try VBScript or DTS newsgroups to get better answer.|||

Sussed it. If I use:

Set WshNetwork = CreateObject("WScript.Network")

It works fine

DTS Package Errors

I have created several DTS packages that run if I execute them imidiatly, but error out if scheduled with the following error...

Executed as user: Domain\Username. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.Most likely it's that it doesn't have access to the drive...

You need to set DTS up so that when you manually run, it will find the same location on the server.

When you run, it runs under the context of your client, when scheduled it runs under the context of the agent id.

Make sure there is the same directory stucture on the server as you have (or vica versa) and make sure to copy the file there...|||Both are being run from the same server.|||What user is the owner of the job. That user may not have rights to execute the DTS package. Also check who the owner of the DTS package is?|||Already verified those and they are good.
The error states which user it is using and it is the same user name that SQL agent is started with. and it has all needed privalges that I can see.|||How did you create the job? Did you right click the job and select schedule?|||Yes|||Is the server SQL 7??|||No, it is SQL server 2000 Enterprise.|||Connections in the package are good? Anything dynamic going on?? Did you get the error message from the job logs??|||I get the same error on all my scheduled DTS jobs, including a test job I created that drops a test table. I find the error in the history of the job after it has run.|||Sorry running out of ideas. Is the owner of the job SA?? not the sql agent|||nope.|||Can you log onto the machine that actually runs the package and execute the package from there? Turned out to be the trick for me -- if I executed from nearly anywhere else there was a DSN set up to map the DNS name to a connection. Just not on the box itself, so EM could execute the package anywhere but on the actual box. I know sounds odd, but it's true...

DTS Package Error - Something simple?

I have a multi-step DTS package that I get the following error when I try to execute it:

Package Error
Error Source : microsoft ole db provider for sql server
Error Description : [DBNETLIB][ConnectionOpen (connect()).]SQL Server does not exist or access denied

Am I missing something obvious? I'm sure it is something simple but I can't seem to get it. Thank you in advance for you assistance.It means the package can't connect to a data source it wants to connect to. Open up the package in Design mode and look at the connection properties. Check that the server name, the authentication method, and the username and password are correct.

DTS Package Error

Hello,
I created a DTS package and got the following error:
There is already an object named 'DF_tblTelica_DateEntered' in the database.
could not create constraint. See previous errors.
Is there a table, query or stored procedure that I can use to find this type
of information?
Thanks in advance,
sck10Hi Sck10,
From your descriptions, I understood that you will meet the error saying
object 'DF_tblTelica_DateEntered' has exists when creating DTS packages.
Have I understood you? Correct me if I was wrong.
Based on my scope, it seems you have created an object named
'DF_tblTelica_DateEntered'. Have you done this? Try to use the following
SELECT statement to find whether it really exists in your database.
SELECT OBJECT_NAME(parent_obj)
FROM sysobjects WHERE name = 'DF_tblTelica_DateEntered'
SELECT *
FROM sysobjects WHERE name = 'DF_tblTelica_DateEntered'
If you do find it, make sure it was useless and then drop it
If you do not find it, please show me you detailed steps about creating
that DTS package. More detailed information, I believe, will make us closer
to the resolution.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Sck10,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

DTS Package Error

Hello,
I created a DTS package and got the following error:
There is already an object named 'DF_tblTelica_DateEntered' in the database.
could not create constraint. See previous errors.
Is there a table, query or stored procedure that I can use to find this type
of information?
Thanks in advance,
sck10
Hi Sck10,
From your descriptions, I understood that you will meet the error saying
object 'DF_tblTelica_DateEntered' has exists when creating DTS packages.
Have I understood you? Correct me if I was wrong.
Based on my scope, it seems you have created an object named
'DF_tblTelica_DateEntered'. Have you done this? Try to use the following
SELECT statement to find whether it really exists in your database.
SELECT OBJECT_NAME(parent_obj)
FROM sysobjects WHERE name = 'DF_tblTelica_DateEntered'
SELECT *
FROM sysobjects WHERE name = 'DF_tblTelica_DateEntered'
If you do find it, make sure it was useless and then drop it
If you do not find it, please show me you detailed steps about creating
that DTS package. More detailed information, I believe, will make us closer
to the resolution.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Sck10,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

DTS Package Error

Hello,
I created a DTS package and got the following error:
There is already an object named 'DF_tblTelica_DateEntered' in the database.
could not create constraint. See previous errors.
Is there a table, query or stored procedure that I can use to find this type
of information?
--
Thanks in advance,
sck10Hi Sck10,
From your descriptions, I understood that you will meet the error saying
object 'DF_tblTelica_DateEntered' has exists when creating DTS packages.
Have I understood you? Correct me if I was wrong.
Based on my scope, it seems you have created an object named
'DF_tblTelica_DateEntered'. Have you done this? Try to use the following
SELECT statement to find whether it really exists in your database.
SELECT OBJECT_NAME(parent_obj)
FROM sysobjects WHERE name = 'DF_tblTelica_DateEntered'
SELECT *
FROM sysobjects WHERE name = 'DF_tblTelica_DateEntered'
If you do find it, make sure it was useless and then drop it
If you do not find it, please show me you detailed steps about creating
that DTS package. More detailed information, I believe, will make us closer
to the resolution.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Sck10,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

DTS Package as a job

I tired to run a DTS package as a job and the job failed right away with the following message in the history of the job.

I'm running the DTS package using the dtsrun cmd in the job.

The error message:

DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713. Process Exit Code 1. The step failed.

W the f_is worng with it.... sorry this is urgentThat means that some object in your dts package has not been registered or is corrupt - Did this package run successfully from the dts designer ?|||Well I have activex scripts, dynamic propeties task, sql tasks in the package. I ran perfect on the development server as a job but on the uat server it gives that error. what r they ways around this?|||Did you copy the package from the development server to this one ? Also, did you try running the package from the dts designer on this server ?|||Originally posted by rnealejr
Did you copy the package from the development server to this one ? Also, did you try running the package from the dts designer on this server ?

Yes i coped the package from dev to uat. no i didnt becuase the pakage is built to be protable ie the connection data sources use (local) so they dont haveto be changed once they r promoted to different enivornments.... so if the package is run from the designer it wont use the uat server but my local server... but if its run as a job it runs on the uat server...|||I would test the package and run it on the actual sql server uat through the dts designer. This will eliminate any issues between the differences in how sql server executes a job versus a dts package.|||Originally posted by rnealejr
I would test the package and run it on the actual sql server uat through the dts designer. This will eliminate any issues between the differences in how sql server executes a job versus a dts package.

I ran the package on the actual server through dts designer and it works fine. but running it as a job still gives me the invalid class string error.

Plz advise.|||Is the owner of the job a sa within sql server ? If it is, what is the login/permissions of the account that is used for the agent service ?|||job owner is sa
login/permissions are administrative rights (all access)

generally what can generate this error?|||This might be related to permissions in the registry. What login is used to run the sql server agent service ? And what permissions does it have on this server ?|||The sql server agent uses system login. I'm guessing the system login has all the rights etc

fyi : it's running SQL7.0, Mdac version 2.5.4403.12

Microsoft SQL Server 7.00 - 7.00.1077 (Intel X86) Sep 6 2002 15:10:15 Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)|||You created the package using SQL 2000 Enterprise manager which has objects like dynamic properties task, that do not exist on SQL 7. You will be able to execute it through enterprise manager on your PC, but not as a job on the SQL 7 Server.|||Thank you!... never realized that

ne alternatives to dynamic task properties in sql7 ?|||Yes, use Active X scripts to assign properties to objects. Like this: Well you'll have to format it better, the small text box messed it up.

Option Explicit
Function Main()

Dim oPKG
Dim oCustomTask

Set oPKG = DTSGlobalVariables.Parent

oPKG.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("fundingTableName").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask.Properties.Item("SourceObjectName").Value = DTSGlobalVariables("tableName").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("exportPath").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_3").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("tableName").Value
Set oPKG = Nothing

Main = DTSTaskExecResult_Success
End Function|||Nice catch shicks.|||No Problem, Anytime.|||Say i have to set the data source of a connection using active x... how would i do that?

for eg: the data source of the connection should equal to a global variable.|||This should do it

Function Main()

Dim oPKG
Dim oConnection

Set oPKG = DTSGlobalVariables.Parent

For Each oConnection in oPKG.Connections
If oConnection.ProviderID = "SQLOLEDB" Then
oConnection.DataSource =DTSGlobalVariables("server").Value
oConnection.Catalog = DTSGlobalVariables("database").Value
oConnection.UserID = DTSGlobalVariables("username").Value
oConnection.Password = DTSGlobalVariables("password").Value
End If
Next

Set oConnection = Nothing
Set oPKG = Nothing

Main = DTSTaskExecResult_Success

End Function|||Life saver!
thx

ne good resources where i could learn more about ActiveX scripts?

thx again|||I think it's VB script, then I just open the dynamic properties task and pick and object and get the property names. I am sure there must be a more efficient way to view the DTS classes, but it works for me. Maybe look for a DTS programming book. I don't know of any off the top of my head, but I am sure they are out there.

DTS package and nested OPENQUERYs?

Is there a way to use nested OPENQUERY's in a DTS package??

I have the following SQL statement (whichs execute without error) that I would like to make a DTS package out of:

INSERT tbl_Sku SELECT AX.sku_id, AX.style_id, AX.style_color_id, AX.style_size_id, AY.color_id, AY.size_master_id from OPENQUERY("SVR-1",'SELECT sku_id, style_id, style_color_id, style_size_id FROM merch.dbo.sku') as AX, OPENQUERY("SVR-2",'SELECT sku_id, style_id, color_id, size_master_id FROM ma.dbo.sku') as AY Where AX.sku_id = AY.sku_id

The problem I am having is the connection object of the DTS package. It will not allow an OPENQUERY inside an OPENQUERY. How do I set up a third comnnection??Your statement does not have an OPENQUERY inside an OPENQUERY. It has two joined OPENQUERY statements.|||I guess I could just repeat the question that I was actually asking. I'm a programmer, not a dba, so my 'nested' terminology was wrong in the last post.

Does anyone have experience with setting up a DTS package to insert into the target table data from 2 other connections, where the connection inserting into the target table is extracting from yet a third connection?|||Either:
1) Your use of OPENQUERY is unnecessary in a DTS package, since you can just set up a connection object within the package for each remote server.
or...
2) Your use of a DTS package is unnecessary, since you could just run this statement from a stored procedure through the Job Scheduler.
The best advice I can give you on DTS is put as little logic as possible into it. It is a quirky utility that is a hodgepodge of poorly integrated technologies. Use it to load data into staging tables, and then perform your logical processing on the data using stored procedures.|||Well, the dbs's at this contract want me to use DTS packages since all the existing stored procedures I am converting into DTS packages do nothing more than truncate the target table and insert data into it.

I already have them running about 15 times faster (taking 15 minutes what was taking almost 4 hrs using sp's) so I want to finish creating a DTS package out of the last 2 sp's I am converting since it is doing the exact same thing and I need it to run at the conclution of the sucess of other processes.

The issue I am having is that I do not know how to use 3 connections to do this. I have one table that is being loaded from tables on two different servers. Although they are currently linked servers, I want to be able to run the existing query using a DTS package. I just do not know how to set up the connections.

I found some references to transformation lookup and was wondering if anyone reading this has done this before.|||By the way, let me just say I have no problem setting up 2 connections and running a query, but I am having problems getting data out of two connections and inserting it into a third connection, which is the target table.|||I already have them running about 15 times faster (taking 15 minutes what was taking almost 4 hrs using sp's)Then likely the sprocs were poorly written.
all the existing stored procedures I am converting into DTS packages do nothing more than truncate the target table and insert data into itAlso a questionable practice.
I don't know all the details of what you are doing, but whether by choice or by direction from the "dbs's" (dba's?), your design does not appear to be following best practices. Avoiding stored procedures in favor of heavy DTS processing too often leads to solutions that look like they were designed by Rube Goldberg, as you appear to be finding out.
I still recommend you DTS from both remote systems into staging tables and then perform your joined insert.
One question...did the original "slow" stored procedures happen to use either cursors or programmatic looping?|||Forget it, I'll try a different forum.

DTS Package - "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION"

When running a step within my DTS package I'm receiving the following
error - "Provider generated code execution exception:
"EXCEPTION_ACCESS_VIOLATION".
I think it may be something to do with my global variable, but I'm not
sure as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.Can anyone help? The below screenshots show my specific settings (note
I'm still getting the same error with these options)
http://www.files2net.com/files/46053431/1.JPG
http://www.files2net.com/files/266513949/2.JPG
http://www.files2net.com/files/266955779/3.JPG

DTS Package - "Provider generated code execution exception: "EXCEPTION_ACCES

When running a step within my DTS package I'm receiving the following error
- "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION"
.
I think it may be something to do with my global variable, but I'm not sure
as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.Can anyone help? The below screenshots show my specific settings (note I'm s
till getting the same error with these options)
http://www.files2net.com/files/46053431/1.JPG
http://www.files2net.com/files/266513949/2.JPG
http://www.files2net.com/files/266955779/3.JPG|||Managed to solve it by changing the settings to the following:
http://www.files2net.com/files/154842170/settings.jpg

DTS Package

Hi there

I've use an example I've found to build the following dts.execution from VB6:

Dim opackage As DTS.Package

Set opackage = New DTS.Package
opackage.LoadFromSQLServer PARS, , , DTSSQLStgFlag_UseTrustedConnection, , , , copyTxt, 0
opackage.Execute
opackage.UnInitialize
Set opackage = Nothing

The problem is when I execute it gives an error saying:
The specified DTS Package ('name='[notspecified]'; ID.Version ={'[notspecified]'},{'[notspecified]'}') does not exist.

Can anyone see what is wrong please? :eek:
ThanksWe had a similar problem here if the DTS package had more than one version. On the DTS packages, right click and go to versions. I would save the existing package under a new name, then delete all versions but one on the package referenced by the application.

DTS Oracle Connection - Importing Data

I have SQL Server 2000 and am using DTS to simply import some data located on a remote Oracle DB.

If I have the following information:

TNS Name:

 ThisApp.world
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS =
 (COMMUNITY = tcpxxx.world)
 (PROTOCOL = TCP)
 (Host = SomeHost)
 (Port = 1234)
 )
 )
 (CONNECT_DATA =
 (SID = App)
 (GLOBAL_NAME = ThisApp.world)
 )
 )

And I also have the name of the Table.

Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:

Data Source: Microsoft ODBC Drive for Oracle
Server:
Username:
Password:

I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?

Your assistance will be greatly appreciated.Given the above information, how can I use Server Explorer in VS.NET 2002? It still prompts me for a Username/Password, which I do not have.

Please help anyone.