DTS execution from T-SQL

I really need help!!!

I built a DTS package that makes data pump from Paradox 7.0 to SQL Server. If I execute this package from Package Designer everything goes fine no matter if Paradox files are located in local or mapped folders.

However, If I execute the package from T-SQL, it crashes when if Paradox files are located in a mapped drive saying "not valid path" (With local folder it works)

Im using xp_cmdshell 'DTS_Run /S "server name" /N "package name" /E'

Can anyone tell me whats wrong with this

ThanksFirst of all check whether the account used have necessary privileges to carry tasks on paradox and on the machine.

Dts Execute Problem

Hi all,
My dts fails in step 3 when i run it from asp.net. But it works fine in when i run in Designer.
In step 3, it reads some data from a text source (on a shared place). And it gives me error :

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error code: 80004005
Step Error Help FileTSFFile.hlp
Step Error Help Context ID:0

I know that when i run it on Designer, it works by my current Windows Loginname. And when i execute dts in asp.net page, it works by SqlAgent's login name. And also i made SqlAgent's Username (as a Domain UserName) a Local Admin in the file shared server.

Any idea?

Regards..

It appears that your ASP.NET application does not have permission to access the flat file.

However, it is very unlikely that your ASP.NET application is running in the context of the SQL Agent account.

How are you launching the package - using the API in your code, or calling sp_start_job?

|||I think my ASP:NET application has permission because i can get list of that folder's files and can edit one of them.
My application runs with my current AD username/password.
I use Interop.DTS class to run Dts. I can run other dts correctly with this method but i can't run this one.

Regards..

DTS Designer Error: The specified module could not be found

Hi folks
I am connecting to a SQL Server 2000 (SP3) from a client Enterprise
Manager. It connects to it, well.
But if I try to open an existing DTS package (located under Local
packages) it throws an error "DTS Designer Error The specified module
could not be found" and it closes.
Same error occurs if I try to open a .dts file also.
Your help is appreciated.
Thanks in advance
Venkat.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
Searching google for you error messages turned up these two possible
solutions:
http://tinyurl.com/66tsu
http://tinyurl.com/6e6jm
John
"Venkatesan" wrote:

> Hi folks
> I am connecting to a SQL Server 2000 (SP3) from a client Enterprise
> Manager. It connects to it, well.
> But if I try to open an existing DTS package (located under Local
> packages) it throws an error "DTS Designer Error The specified module
> could not be found" and it closes.
> Same error occurs if I try to open a .dts file also.
> Your help is appreciated.
> Thanks in advance
> Venkat.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

DTS Designer Error - Specified file not found

Hi there,

lately I had the following message on win2000 sp4 with sqlserver2000 sp3a: "DTS Designer Error - Specified file not found".
Then in the DTS editor the Task menu objects are missing and it is not possible to link a source and a target using Transform Data Task.

Before it used to work fine.

I have done the following:

- installing again last sp: same issue again
- reinstalling sqserver with a registry deep cleaning: same issue again
- started disabled windows services: same issue again

any idea?

Carsten

edit:
Could it be possible that a lately installed fix for Win2K interferes with DTS-Designer?Finally found a solution for this problem!

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=112035

DTS Designer error

when I open a DTS package (or create a new one) I get the error:
DTS Designer error
The system cannot find the file specified
then the package opens, but there are no objects in the "tasks" pane (the
"connection" pane looks ok). All the task objects in the package show up
though (and can be edited too) but no new ones can be added (since also under
the Task menu, only the items "Register custom task..." and "Unregister
custom task..." exist).
So the question is: which dlls are the missing tasks so that to register
them back?
Also, any ideas why they dissapeared?
It's generally from having a mix of different service pack
levels. See the following article:
Service Pack and Version Compatibility Problems
http://www.sqldts.com/default.aspx?223
-Sue
On Wed, 25 Jan 2006 02:13:02 -0800, "vsiat"
<vsiat@.discussions.microsoft.com> wrote:

>when I open a DTS package (or create a new one) I get the error:
>DTS Designer error
>The system cannot find the file specified
>then the package opens, but there are no objects in the "tasks" pane (the
>"connection" pane looks ok). All the task objects in the package show up
>though (and can be edited too) but no new ones can be added (since also under
>the Task menu, only the items "Register custom task..." and "Unregister
>custom task..." exist).
>So the question is: which dlls are the missing tasks so that to register
>them back?
>Also, any ideas why they dissapeared?
|||It didnt work. I checked both
http://www.sqldts.com/default.aspx?223 and the related link
http://support.microsoft.com/Default.aspx?kbid=274716
but they all seem to refer to SQL Server 7.0 issues.
I have SQL 2000 SP4 (both on the server and the client tools in my pc) and
the packages were created with this version.
"Sue Hoegemeier" wrote:

> It's generally from having a mix of different service pack
> levels. See the following article:
> Service Pack and Version Compatibility Problems
> http://www.sqldts.com/default.aspx?223
> -Sue
>
|||Try opening the packages from another box. If you can open
them fine on other boxes then it's specific to that box. You
could first try reregistering dtsui.dll. Other than that,
they rest are listed in the following:
http://www.sqldts.com/default.aspx?225
-Sue
On Thu, 26 Jan 2006 03:31:02 -0800, "vsiat"
<vsiat@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>It didnt work. I checked both
>http://www.sqldts.com/default.aspx?223 and the related link
>http://support.microsoft.com/Default.aspx?kbid=274716
>but they all seem to refer to SQL Server 7.0 issues.
>I have SQL 2000 SP4 (both on the server and the client tools in my pc) and
>the packages were created with this version.
>
>"Sue Hoegemeier" wrote:
|||Oh yes, I forgot to mention that: The problem is ONLY on my pc. When I open
the packages from the Enterprise Manager on the server, or any other pc with
the client tools, everything is ok.
The dlls listed in the link (and also the ones in the redistr.txt of SP4)
are not the missing tools of the DTS Designer, they seem to only contain
supporting code to run DTS packages from other applications (including the EM
itself). I register them, but nothing changes on the interface.
I have also re-installed the client tools and re-applied SP4, but still
nothing.
"Sue Hoegemeier" wrote:

> Try opening the packages from another box. If you can open
> them fine on other boxes then it's specific to that box. You
> could first try reregistering dtsui.dll. Other than that,
> they rest are listed in the following:
> http://www.sqldts.com/default.aspx?225
> -Sue
|||Additionally, here are some other people with the same problem (none got any
lucky):
http://groups.google.com/group/micro...0e3c34fa5a0 9
http://groups.google.com/group/micro...d930f85a8ba 3
http://groups.google.com/group/micro...40731b4429b a
|||I know others have reported similar issues.
I believe Dtsui has the icons.
Only other thing I can think of to try is reapplying the
last service pack on your PC.
-Sue
On Fri, 27 Jan 2006 01:16:02 -0800, "vsiat"
<vsiat@.discussions.microsoft.com> wrote:

>Additionally, here are some other people with the same problem (none got any
>lucky):
>http://groups.google.com/group/micro...0e3c34fa5a0 9
>http://groups.google.com/group/micro...d930f85a8ba 3
>http://groups.google.com/group/micro...40731b4429b a
|||I'm having this issue but slightly different situation. When I create a new DTS package or edit an existing package I get a messagebox "DTS Designer Error" with text "Access is denied." The twist is that this only happens to me when I log in to windows as a user joined to a domain. When I log in to windows as the local administrator everything works correctly.
Obviously it has to do with permissions, but whats odd is that I have added the domain user to the (local) Administrators group specifically to *have* unrestricted rights to my machine. Somehow, in this particular instance, it seems EM is attempting to list the DTS tasks installed in a way that windows,domain security,group policies are disallowing.
AFAIK, the dts tasks are enumerated in the registry. So I don't understand how file permissions could play a role.sql

DTS designer error

When I open my DTS package at my PC ( Client ) .There is an error message show like below .
DTR Designer Error.
There was a problem retriving the list of OLE DB providers.
No. Provider is selected .TO continue,you must select a valid provider.
Please helpHowdy

Delete the server registration in EM and re-register the server.

Post back if problems.

Cheers

SG.|||What is the level of service pack of SQL on the client machine?
Ensure to maintain similar service pack on Server and on client as well.|||Originally posted by sqlguy7777
Howdy

Delete the server registration in EM and re-register the server.

Post back if problems.

Cheers

SG.

I have already done as your mention but it still show the same error message

Any idea ??|||Originally posted by Satya
What is the level of service pack of SQL on the client machine?
Ensure to maintain similar service pack on Server and on client as well.

How can I check the service pack verion of both server and client ? Please introduce me

Thanks|||SELECT serverproperty('ProductLevel')

Regards!!|||select @.@.version;

This will give you the details of OS level as well as SQL Server level.|||This causes due to .DLL mismatch or even MDAC version between client and server machines, so ensure you have all the similar levels.

This @.@.Version or Serverproperty will give you version on server for SQL Server and on client machine ensure to run service pack for SQL again which might resolve the issue.

DTS designer

64b SQL2005 EE and all clent tools installed on the same server. Logged as
local admin user Im trying to create a new connection in DTS Designer but
after succesful test of the connection it errors out with
The new connection manager couldnt be created. Additional info : Exeption
from HRESULT 0xC0010014
Do you know if the client tools are installed by default in x86 Program
files folder and is it possible uninstalling and reinstalling in Program
files will fix the issue?
Any idea whats the problem and how to fix it ?
In our case the problem was SP1 unfinished /unsuccesful instalation. After
sql2005 setup REBOOT and when is up stop all sql services from Control panel
and then install SP1. Ensure you dont get open files errors if so close the
guilty apps and then continue and if so reapply the SP1 once again.
"Ellie" wrote:

> 64b SQL2005 EE and all clent tools installed on the same server. Logged as
> local admin user Im trying to create a new connection in DTS Designer but
> after succesful test of the connection it errors out with
> The new connection manager couldnt be created. Additional info : Exeption
> from HRESULT 0xC0010014
> Do you know if the client tools are installed by default in x86 Program
> files folder and is it possible uninstalling and reinstalling in Program
> files will fix the issue?
> Any idea whats the problem and how to fix it ?
>

DTS ActiveX Script Task..vanished

I have no idea what's going on.. but when I open the SQL DTS designer.. there's no ActiveX Script task on the left toolbar anymore. It used to be there. How do I get it back?? I remember playing around with some dts dll files on my machine... maybe I did something that made it go away (note: the DB server is not on my machine.. I just run Enterprise Manager on my machine to access it). Should I reinstall Enterprise Manager?OK, weird. I registered a new task, I pointed to axscphst.dll, having a feeling that was the ActiveX stuff I needed. It was.. ActiveX Script Task is back, along with many other tasks.. but they all have a strange page icon now. Not a huge issue really, but how do I get these tasks to their original appearance ?

DTS 2000 package designer and SQL Server 2005

I am not sure where to ask this question. So I figured here may be ok

I have several DTS 2000 packages and I have SQL 2005 installed on my local machine. However the rest of the company is still using SQL 2000 (for now). So I do not want to convert them to ssis yet. One of the things we just did to the packages is make all flat files use \\localhost and any servers point to (local). Now in ssis this works fine but using the DTS 2000 package designer it does not like either of these at all. The reason we wanted to do this is so when we switch out servers there is no changes to our DTS packages.

I have found some problems using \\localhost as a UNC path in SSIS packages (for whatever reason it occassionally returned a duplicate name exception). Instead, try using \\127.0.0.1\ssis , which is essentially a loopback share onto the package execution host machines. Naturally, you would need to create the share on each machine. I haven't used or looked at DTS in eons so I couldn't tell you whether that works with DTS let alone migration issues.|||

yes the 127.0.0.1 worked for paths, however the (local) for servername does not. I Think it is a bug because the DBA that has SQL Enterprise manager can put (local) for servers and localhost for paths. But when I use SQL Server management studio (2005) and the DTS 2000 package designer neither works (except like I said 127.0.0.1).

Now if I build a new ssis then I can use (local) but you are right I think you still have to use 127.0.0.1 for paths.

Thanks for your reply.

DTS 2000 Designer Components

After installing the DTS 2000 designer components from the feature packI get the following error message when trying to open a package:

-

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

The thing is that I have installed them!! so what's going on here, is there a registry key I need to remove or something?, I've uninstalled and re-installed them, rebooted the server numerous times but still no joy, SQL Server just tells me that they're not installed, as you can imagine this is a real pain as I cannot do anything with my legacy packages on the 2005 server.

I cannot just migrate the packages from my 2000 server as they are in daily use on a production server. Is there an option to migrate/copy packages but leave the source packages untouched?

Help!

Yay, I seem to have stumbled across a solution for this:-

1. Uninstall SQL Server 2005 Backward Compatibilty

2. Uninstall SQL Server 2000 DTS Designer Components

3. Re-install SQL Server 2005 Backward Compatibilty

4. Re-install SQL Server 2000 DTS Designer Components

5. On the SQL Server 2000, open te DTS package and save it to the 2005 Server ( it will be placed in the <server name>\Management\Legacy\Data Transformation Services folder)

6. Edit the package on the 2005 server, NB. if you make any changes and save the package, the designer will take it upon itself to kindly auto-arrange your package so the package you had taken time to lay out in a nice readable format now resembles a bowl of spaghetti.

I found that moving the 2000 DTS packages in any other way than in step 5 resulted in the designer throwing up all sort of errors, although it did still work until you actually tried to save it. In my opinion, this is far too flaky and I am very hesitant to upgrade to SQL 2005 and leave my DTS packages in the hands of this designer.

|||I am trying to to the same thing, where do I find the download "SQL Server DTS Designer Components"? Second question, did you get it to work?|||

The DTS designer components are part of the SQL Server 2005 feature pack, just do a search for it on the microsoft website.

I found that the designer components work ok for some of my 2000 packages but not for others. I don't know why it behaves like this and there is no pattern to it, all I know is that it is a pain in the backside.

hope this helps

|||

Feature Pack for Microsoft SQL Server 2005 - November 2005 http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Which includes -

Microsoft SQL Server 2000 DTS Designer Components http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_DTS.msi

|||Thank you this solved my problem.

DTS & SQL Server Management Studio Express

Can you use the DTS download package to run the DTS Designer from within SQL Server Management Studio Express? I don't seem to be able to get it to show up in SQL Server Management Studio Express after I dnld it.

I do not believe this is possible no.|||

Please post back if you find a way. I was looking for the same thing. This would give us a roundabout deployment option for dtsx packages on sql express, which would would be unbelievably great.

Any ideas why we can't currently redistribute dtsx packages and the new dts runtime? Being able to leverage the wonderful new SSIS package designer to create standalone data transformations would be truly outstanding!

BTW, the inclusion of reporting services and the report builder tool in sql express is just great. Thanks for providing this!

Regards,

Lacan.