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.