Dts Run Problem

Hi all,

I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.

But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

My ExecuteDts Function is :

public static void ExecuteDTS(string PackageName,params object[] DtsVariableValues) {string ServerName = ConnectionNames.OlapServerName;string ServerUserName = ConnectionNames.OlapUserName;string ServerPassword = ConnectionNames.OlapPassword;object pVarPersistStgOfHost =null; DTS.PackageClass DtsPackage =new DTS.PackageClass(); DtsPackage.LoadFromSQLServer(ServerName, ServerUserName, ServerPassword, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default ,null,null,null, PackageName,ref pVarPersistStgOfHost);try {string[] DtsVariableNames =new String[DtsPackage.GlobalVariables.Count];int VariableIndex = 0; DtsPackage.UseTransaction =true;foreach (GlobalVariable globalin DtsPackage.GlobalVariables) { DtsVariableNames[VariableIndex] = global.Name; DtsPackage.GlobalVariables.Remove(global.Name); VariableIndex++; } VariableIndex = 0;foreach (String DtsVariableNamein DtsVariableNames) { DtsPackage.GlobalVariables.AddGlobalVariable(DtsVariableName, DtsVariableValues.GetValue(VariableIndex)); VariableIndex++; } DtsPackage.Execute(); }catch (Exception ex) { //Console.WriteLine(ex.Message); }finally { DtsPackage.UnInitialize(); DtsPackage =null; } }

Any idea?

Regards..

sourvil:

Hi all,

I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.

But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

Can you give us more details about the two machines you are using?|||

My 2 dts are on the same machine, on the same sql server.

Hmm, i think also it would be permission issue but i could not solve it yet?

|||

I think if you give the DTC creater local admin previleges will solve the issue.

Good luck.

|||

I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.

I could not solve it yet?

Regards..

|||

sourvil:

I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.

I could not solve it yet?

Regards..

I think you typed the password incorrectly or Did you forget the sa password? (Try to login on the SQL Server, will you will be able to login successfully?).

Good luck..

|||

Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.

...

Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error Code: 80004005

...

Any idea?

Regards..

|||

sourvil:

Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.

...

Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error Code: 80004005

...

Any idea?

Regards..

Check out these links:
http://www.computerperformance.co.uk/Logon/code/code_80004005.htm
http://www.lazydba.com/sql/1__7259.html
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html

Good luck.

|||

Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.

I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

|||

sourvil:

Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.

I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

I guess it related to a permission on the folder where the filer is reside.
Make sure ASPNET user has the needed permissions on that folder (maybe this is the why its fail not sure).

Good luck.

|||

Ok, i will not solve it :(

But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

|||

sourvil:

Ok, i will not solve it :(

But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

Your are welcomesourvil.

Suggestion (will not solve the problem but will help you in the approach you decided to go with):

Have two DTS, one is the real one (has all the logic and transdormations) and the other have a task to run the first one.

This will help you a lot I guess,

Good luck.

DTS Restore from a file

We have a production server and a bunch of development servers. The
Production server is located in a NOC (Network Operations Center)
facility and is physically unaccessible on a regular basis. There are
DTS packages on the production server which we'd like to pull down to
our Dev servers.

I was able to save the DTS package to a file and downloaded it to our
Dev Servers. But how in the world do I restore it?? I'm a fairly
technical guy and for the life of me, I'm not able to figure this one
out! How do I restore the DTS package which has been stored into a
..dts file?

Please help.

Thank you in advance,

-Umar.I think what you want to know is this:

Right-click on the "Data Transformation Services" FOLDER icon and select
"Open Package..." from the options. This brings up a dialog box to hunt for
your DTS file(s).

"Umar Farooq" <UmarAlFarooq@.gmail.com> wrote in message
news:1110475810.551932.46600@.f14g2000cwb.googlegro ups.com...
> We have a production server and a bunch of development servers. The
> Production server is located in a NOC (Network Operations Center)
> facility and is physically unaccessible on a regular basis. There are
> DTS packages on the production server which we'd like to pull down to
> our Dev servers.
> I was able to save the DTS package to a file and downloaded it to our
> Dev Servers. But how in the world do I restore it?? I'm a fairly
> technical guy and for the life of me, I'm not able to figure this one
> out! How do I restore the DTS package which has been stored into a
> .dts file?
> Please help.
> Thank you in advance,
> -Umar.

DTS points to Named Pipe or TCP/IP?

Hello all,
When I used DTS, I found out that network library pointed Named pipes at
Process Info in Current activity panel. As my understanding, the network
library should points TCP instead.
What does the network library point to when DTS is used?
Should that library point to TCP/IP?
Which one is faster Named Pipe or TCP/IP?
How can I change order?
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...nnect/200508/1
Network library used depends on the client and the server.
Look at the client network utility and the server network
utility.
TCP/IP would probably be preferred in most cases
TCP/IP is normally faster
Try changing the order of the protocols on the client where
the DTS package is executed. Or try creating a TCP/IP alias
using the client network utility.
-Sue
On Thu, 25 Aug 2005 15:29:54 GMT, "Do Park via
droptable.com" <forum@.droptable.com> wrote:

>Hello all,
>When I used DTS, I found out that network library pointed Named pipes at
>Process Info in Current activity panel. As my understanding, the network
>library should points TCP instead.
>What does the network library point to when DTS is used?
>Should that library point to TCP/IP?
>Which one is faster Named Pipe or TCP/IP?
>How can I change order?
>
>Thanks in advance,
>Do.

DTS points to Named Pipe or TCP/IP?

Hello all,
When I used DTS, I found out that network library pointed Named pipes at
Process Info in Current activity panel. As my understanding, the network
library should points TCP instead.
What does the network library point to when DTS is used?
Should that library point to TCP/IP?
Which one is faster Named Pipe or TCP/IP?
How can I change order?
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...onnect/200508/1Network library used depends on the client and the server.
Look at the client network utility and the server network
utility.
TCP/IP would probably be preferred in most cases
TCP/IP is normally faster
Try changing the order of the protocols on the client where
the DTS package is executed. Or try creating a TCP/IP alias
using the client network utility.
-Sue
On Thu, 25 Aug 2005 15:29:54 GMT, "Do Park via
droptable.com" <forum@.droptable.com> wrote:

>Hello all,
>When I used DTS, I found out that network library pointed Named pipes at
>Process Info in Current activity panel. As my understanding, the network
>library should points TCP instead.
>What does the network library point to when DTS is used?
>Should that library point to TCP/IP?
>Which one is faster Named Pipe or TCP/IP?
>How can I change order?
>
>Thanks in advance,
>Do.

DTS packages fail after server migration

Hi All
My DTS packages fail after i have migrated my sql server from a NT based
network to a windows 2003 based network and unattached the old domain, I
know that the users that was once on the server as %old domain%\user will no
longer have any rights on the sql server, But how can i change the ownership
of those DTS packages to %New Domain%\New AD User.?
some input would be great.
thanksHave you checked SQLDTS.com. I believe package ownership issues are covered
on that site.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Todd" <Todd_c_wright@.hotmail.com> wrote in message
news:uVew3tSDEHA.3348@.TK2MSFTNGP11.phx.gbl...
Hi All
My DTS packages fail after i have migrated my sql server from a NT based
network to a windows 2003 based network and unattached the old domain, I
know that the users that was once on the server as %old domain%\user will no
longer have any rights on the sql server, But how can i change the ownership
of those DTS packages to %New Domain%\New AD User.?
some input would be great.
thanks|||You can change the owner using the undocumented
sp_reassign_dtspackageowner but the package ownership
probably has nothing to do with the packages failing. You
can sometimes run into issues when someone who isn't the
owner and isn't a sysadmin attempts to modify and save
packages owned by someone else but that's about it.
You can find more info on this at the site Vyas referred you
to:
Package Ownership Issues
http://www.sqldts.com/default.aspx?212
Are these scheduled packages that are failing or do they
fail when run manually as well? What are the errors?
-Sue
On Fri, 19 Mar 2004 09:56:27 +1300, "Todd"
<Todd_c_wright@.hotmail.com> wrote:

>Hi All
>My DTS packages fail after i have migrated my sql server from a NT based
>network to a windows 2003 based network and unattached the old domain, I
>know that the users that was once on the server as %old domain%\user will n
o
>longer have any rights on the sql server, But how can i change the ownershi
p
>of those DTS packages to %New Domain%\New AD User.?
> some input would be great.
> thanks
>

DTS package - Network error

Hey, I am trying to execute a DTS package that imports a flat text file from a remote server to the database.
It works fine from the Enterprise Manager, but form the Query analyzer and my code I am getting the following error:

Error string: Error opening datafile: The network name cannot be found.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Detail Records:
Error: 67 (43); Provider Error: 67 (43)
Error string: Error opening datafile: The network name cannot be found.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0

Here is how I execute the dts:
EXEC master..xp_cmdshell 'dtsrun /S <server> /N <package_name> /E'

The file that I am trying to imports is on a different server
Whenever I use the "My Network Places", I can access the file (\\server\folder\filaname).
Also, once again when I execute the package from the enterpise manager it works fine. Any ideas?Are the SQL Server Startup account and the account you are logged in different ?|||Good point but now luck.
I just tried as the same user ("sa") from both enteprise manager and query anlyzer and still doesn't work from the query analyzer.|||Do the startup service accounts (for sql server and agent) have access to that network drive ? Also, what happens if you run dtsrun from the command line ?|||I would guess that your server is a seperate machine from the machine you are running enterprise manager on.

I would guess that this machine does not have access to the network drive you are trying to use.

Is your file referenced using UNC or are you trying to use a mapped drive?

DTS Network Traffic

Hi,
I am trying to confirm a hypothesis on the Network traffic that a certain
DTS will generate, my environment is configured as follows:
Server A: SQL Server Instance to act as source
Server B: SQL Server Instance to execute DTS Package
Server C: File Server at remote location to be target server
If I run a DTS package in Server B to extract information from Server A
and generate a fixed length flat file in Server C. Is it valid to say that,
all the information required to build the flat file will travel on my network
from Server A to Server B first and then it will be dumped in the flat file
on Server C?
Is there any way to still use Server B to execute the DTS package but, have
the network traffic go directly from Server A to Server C?
Thanks a lot!
Ignacio
why dont you just have serverA execcute the DTS job straight to C?
"i-DLT" <iDLT@.discussions.microsoft.com> wrote in message
news:6871C29D-34CF-4D9C-85D8-FE08C3E420E9@.microsoft.com...
> Hi,
> I am trying to confirm a hypothesis on the Network traffic that a certain
> DTS will generate, my environment is configured as follows:
> Server A: SQL Server Instance to act as source
> Server B: SQL Server Instance to execute DTS Package
> Server C: File Server at remote location to be target server
> If I run a DTS package in Server B to extract information from Server A
> and generate a fixed length flat file in Server C. Is it valid to say
> that,
> all the information required to build the flat file will travel on my
> network
> from Server A to Server B first and then it will be dumped in the flat
> file
> on Server C?
> Is there any way to still use Server B to execute the DTS package but,
> have
> the network traffic go directly from Server A to Server C?
> Thanks a lot!
> Ignacio
|||They are on different newtorks and have different administrators, Server A's
security is much more strict than Server B which I administer.
"Immy" wrote:

> why dont you just have serverA execcute the DTS job straight to C?
> "i-DLT" <iDLT@.discussions.microsoft.com> wrote in message
> news:6871C29D-34CF-4D9C-85D8-FE08C3E420E9@.microsoft.com...
>
>

DTS Network Traffic

Hi,
I am trying to confirm a hypothesis on the Network traffic that a certain
DTS will generate, my environment is configured as follows:
Server A: SQL Server Instance to act as source
Server B: SQL Server Instance to execute DTS Package
Server C: File Server at remote location to be target server
If I run a DTS package in Server B to extract information from Server A
and generate a fixed length flat file in Server C. Is it valid to say that,
all the information required to build the flat file will travel on my networ
k
from Server A to Server B first and then it will be dumped in the flat file
on Server C?
Is there any way to still use Server B to execute the DTS package but, have
the network traffic go directly from Server A to Server C?
Thanks a lot!
Ignaciowhy dont you just have serverA execcute the DTS job straight to C?
"i-DLT" <iDLT@.discussions.microsoft.com> wrote in message
news:6871C29D-34CF-4D9C-85D8-FE08C3E420E9@.microsoft.com...
> Hi,
> I am trying to confirm a hypothesis on the Network traffic that a certain
> DTS will generate, my environment is configured as follows:
> Server A: SQL Server Instance to act as source
> Server B: SQL Server Instance to execute DTS Package
> Server C: File Server at remote location to be target server
> If I run a DTS package in Server B to extract information from Server A
> and generate a fixed length flat file in Server C. Is it valid to say
> that,
> all the information required to build the flat file will travel on my
> network
> from Server A to Server B first and then it will be dumped in the flat
> file
> on Server C?
> Is there any way to still use Server B to execute the DTS package but,
> have
> the network traffic go directly from Server A to Server C?
> Thanks a lot!
> Ignacio|||They are on different newtorks and have different administrators, Server A's
security is much more strict than Server B which I administer.
"Immy" wrote:

> why dont you just have serverA execcute the DTS job straight to C?
> "i-DLT" <iDLT@.discussions.microsoft.com> wrote in message
> news:6871C29D-34CF-4D9C-85D8-FE08C3E420E9@.microsoft.com...
>
>