Failed to schedule DTS in SQL 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

sql

failed to register dll files needed for DTS

I found the information of installing DTS service without installing SQL server on the disc:

INSTALLATION NOTES FOR DATA TRANSFORMATION SERVICES (DTS)
------------------
The following files must be registered using the regsvr32.exe utility:
dtsffile.dll
dtspkg.dll
dtspump.dll
axscphst.dll

Example: regsvr32 80\Tools\Binn\dtsffile.dll

The sqlunirl.dll file should reside in the system folder (i.e., \winnt\system32 or \windows\system).

when I attempted to register the dlls, the message
:"loadLibrary("...\dtsffile.dll")failed - The speified module could not be
found.

is there anything I have to do before I can get the dll registered?

Thanks in advance.problem solved, please consult here:

http://www.sqldts.com/default.aspx?6,105,225,0,1

Failed DTS Package locks user out of SQL2005 (Windows Authentication) ... for good!

Would be interested in any advice or comment on the issue we are experiencing with SQL 2005.

In order to test some DTS package migrations, we simply created a DTS package on SQL 2000 (using Export) to copy all database objects from one database to another.

Then we restored the source database on SQL 2005 server, migrated the DTS package, and tried to execute it.

The package migrated 'fine', ran part way, and then failed. Since then we have been unable to connect to the server (Database Engine, or Integration Services) using Windows Authentication. (Error 18456, Sev 16, State 11).

We can connect to the Database Engine using SQL Authentication (but not to Integration Services which only allows Windows Authentication).

We have been able to replicate this consistently - every annoying time we've tried it!

(Reinstalling SQL Server does 'fix' the issue ... until you try a similar package again).

Are you saying that you can't connect to Integration Services? From your post the Database Engine is fine, and 'locks user out' is misleading as the account is not locked out.

What does the package try or fail to do (and which step)?
What appears in the application event log when you fail to connect to IS?
Is IS service still running?|||

Thanks for your reply. Allow me to attempt to answer your queries.

Both Integration Services and Database Engine (MSSQLSERVER) are still running. As stated, we can connect to the Database Engine using SQL Authentication, but Windows Authentication (no matter what account) no longer works to either the Database Engine or Integration Services.

OK, perhaps my terminology is not quite correct - it's not an account lock-out, but that's pretty much the symptom. Perhaps I should say "Windows Authentication is broken"?

Sample Event Log (descending timestamp order ie. same as in event log viewer):

MSSQLSERVER Failure Audit (4) 18456 SERVER\Administrator Login failed for user 'SERVER\Administrator'. [CLIENT: <local machine>]

SQLISPackage Error None 12291 SERVER\Administrator SERVER "Package ""DTS_TEST"" failed."

SQLISPackage Error None 12291 SERVER\Administrator SERVER "Package ""ShellPackage"" failed."

SQLISPackage Information None 12288 SERVER\Administrator SERVER "Package ""ShellPackage"" started."

SQLISPackage Information None 12288 SERVER\Administrator SERVER "Package ""DTS_TEST"" started."

At this stage I can't determine which step in the package is broken, but that's not my biggest issue at this point. Why Windows Authentication "breaks", and how to fix it is my real concern.

Fail to run as job but success execute in VS2005

hi all,

please help,

i using Execute DTS 2000 Package Task to create a package.dtsx, and execute successful in VS2005

but when i created it as a job and it fail to run.

the package.dtsx is calling a package2000.dts, which having 2 steps to run.

i execute the job by step, and the i found that the 1st job is running successful but the 2nd job was fail.

may i know where can i found the error log? or any where to debug it?

please help......

thanks 1st.....

Hi Khin,

* Note that configuring log for SSIS will not provide the log for Individual steps run in the Execute DTS 2000 Package task.

* Configure a Separate log for the DTS file enclosed by opening it in DTS designer, right click -> Properties ->Logging -> <Give a file Path to Store this log file >.

Thanks

Subhash Subramanyam

fail an execute sql task

I have a dts package that is essentially three transformation tasks that creates 3 text files. The transform data is accomplished through an sql query. Before I execute the transform tasks I would like to peform a couple of simple existence checks to verify data: i.e,

if exists (select * from table where week_ending = endoflastweek(getdate())

I could put the statement into the sql (if exists, begin/end) of each of the transformation tasks and prevent the process from executing, but it would be easier to put it into an "execute sql task" and if it succeeds, fail the task or make some kind of declaration not to proceed. I'm not sure how to do this...I know this can be done with an activeX script,

Main = DTSTaskExecResult_Failure

Is there a way to do this in an execute sql task step?I am not sure if you are looking for raiserror or return here. Look up both in Books Online, and see what you think. I am leaning raiserror.

Extracting info regarding ETL's

Dear all,
I'm pursuing for a method/query or any kind of task being able to find all
those DTS which own Oracle connections inside. It's very hard open one and
one (500)!!
Does anyone ever experienced or face up such requirement?
Thanks for your time and any input will be welcomed,
EnricI don't think it's possible to display dependencies of a DTS Package as it's
application code rather than SQL objects.
You may be able to save the DTS Packages as a Visual Basic file and then
search within the file. I have never tried this though.
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D0217DB6-3099-4664-ABE5-BE383B825C60@.microsoft.com...
> Dear all,
> I'm pursuing for a method/query or any kind of task being able to find all
> those DTS which own Oracle connections inside. It's very hard open one and
> one (500)!!
> Does anyone ever experienced or face up such requirement?
> Thanks for your time and any input will be welcomed,
> Enric|||hi again,
Well, in a fact it's possible instancing dtspkg.dll with VB
and navigate through a specific DTS and obtaining the value for the global
variables and so on... I am trying to avoid this way, it's useless.
Thanks anyway,
"Ryan" wrote:

> I don't think it's possible to display dependencies of a DTS Package as it
's
> application code rather than SQL objects.
> You may be able to save the DTS Packages as a Visual Basic file and then
> search within the file. I have never tried this though.
> --
> HTH. Ryan
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D0217DB6-3099-4664-ABE5-BE383B825C60@.microsoft.com...
>
>|||I hope that the next version avoid this kind of situations.
"Enric" wrote:
> hi again,
> Well, in a fact it's possible instancing dtspkg.dll with VB
> and navigate through a specific DTS and obtaining the value for the global
> variables and so on... I am trying to avoid this way, it's useless.
> Thanks anyway,
> "Ryan" wrote:
>

Extracting Cube detailed info

Hello,

Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.

Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.

-Lawrence

If you are using Analysis Services 2000, yes, using DSO from a DTS package is a good option.

If you are using Analysis Services 2005, then use AMO (Microsoft.AnalysisServices.dll), I can post sample code, let me know please.

Adrian Dumitrascu

|||

Hello Adrian,

If you could post some sample DSO & AMO code for 2000 & 2005, that would be much appreciated. This would get me started as I don't know much about the object schema for DSO nor AMO. Let me know how to get to your code site.

Sincerely,

-Lawrence

|||

Hi,

here is a sample - without any warranty. For more information see http://msdn2.microsoft.com/en-us/library/ms345089.aspx:

using System;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.IO;

using Microsoft.AnalysisServices;

namespace GetCubeInfo

{

class Program

{

static void Main(string[] args)

{

string dateFormat = "yyyy-MM-dd HH:mm:ss";

try

{

FileStream stream;

stream = File.Create(@."GetCubeInfo.xml");

XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

// Causes child elements to be indented

writer.Formatting = Formatting.Indented;

// Report element

writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");

writer.WriteStartElement("Server");

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\YUKON");

writer.WriteAttributeString("Name", null, server.Name);

writer.WriteAttributeString("ReportCreated", null, DateTime.Now.ToString(dateFormat));

// Get the Adventure Works cube(s)

foreach (Database database in server.Databases)

{

writer.WriteStartElement("Database");

writer.WriteAttributeString("Name", null, database.Name);

writer.WriteAttributeString("CreatedTimestamp", null, database.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, database.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, database.LastProcessed.ToString(dateFormat));

writer.WriteAttributeString("LastUpdate", null, database.LastUpdate.ToString(dateFormat));

foreach (Cube cube in database.Cubes)

{

writer.WriteStartElement("Cube");

writer.WriteAttributeString("Name", null, cube.Name);

writer.WriteAttributeString("CreatedTimestamp", null, cube.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, cube.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, cube.LastProcessed.ToString(dateFormat));

writer.WriteEndElement(); // Cube

}

foreach (Dimension dimension in database.Dimensions)

{

writer.WriteStartElement("Dimension");

writer.WriteAttributeString("Name", null, dimension.Name);

writer.WriteAttributeString("CreatedTimestamp", null, dimension.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, dimension.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, dimension.LastProcessed.ToString(dateFormat));

writer.WriteEndElement(); // Dimension

}

writer.WriteEndElement(); // Database

writer.Flush();

}

writer.WriteEndElement(); // Server

writer.Flush();

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

|||

Hello,

I really appreciate the AMO sample code! However, my cubes are currently in 2000, so I would wondering if someone could provide some sample DSO code like above. Output to text file would be good enough.

BTW, to setup automation for the AMO code above, would SSIS be the best place to setup the script? I am thinking either in "Script Component" or "ActiveX" tasks. Let me know if there is a better method.

Sincerely,

-Lawrence

|||

Hi Lawrence,

I strongly recommend you to use the Script Component if you run this code within a SSIS package, because you can use Visual Basic.Net and can use directly the (managed) AMO interface. The ActiveX task is only for backward compatibility reasons. For new code you should use the new ones otherwise you will have migration efforts in the future.

Whether to put it into a SSIS Package or not depends on your requirements and what you want to do with the data you have just gathered in that task. If it is part of your workflow/ETL process, or if you want to use some of the results in other steps/flows, or if you want to use the configuration support of SSIS, or if you want to use the scheduling support with the SQL Server Agent, or, or, or then the answer will be easy (Yes).

Regards,

Bertil

|||

Anyone could provide a sample script of the DSO objects that's related to what I am trying to gather here? I don't think AMO objects would work on 2000 cubes.

Your help is appreciated!

-Lawrence

Extracting Cube detailed info

Hello,

Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.

Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.

-Lawrence

If you are using Analysis Services 2000, yes, using DSO from a DTS package is a good option.

If you are using Analysis Services 2005, then use AMO (Microsoft.AnalysisServices.dll), I can post sample code, let me know please.

Adrian Dumitrascu

|||

Hello Adrian,

If you could post some sample DSO & AMO code for 2000 & 2005, that would be much appreciated. This would get me started as I don't know much about the object schema for DSO nor AMO. Let me know how to get to your code site.

Sincerely,

-Lawrence

|||

Hi,

here is a sample - without any warranty. For more information see http://msdn2.microsoft.com/en-us/library/ms345089.aspx:

using System;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.IO;

using Microsoft.AnalysisServices;

namespace GetCubeInfo

{

class Program

{

static void Main(string[] args)

{

string dateFormat = "yyyy-MM-dd HH:mm:ss";

try

{

FileStream stream;

stream = File.Create(@."GetCubeInfo.xml");

XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

// Causes child elements to be indented

writer.Formatting = Formatting.Indented;

// Report element

writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");

writer.WriteStartElement("Server");

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\YUKON");

writer.WriteAttributeString("Name", null, server.Name);

writer.WriteAttributeString("ReportCreated", null, DateTime.Now.ToString(dateFormat));

// Get the Adventure Works cube(s)

foreach (Database database in server.Databases)

{

writer.WriteStartElement("Database");

writer.WriteAttributeString("Name", null, database.Name);

writer.WriteAttributeString("CreatedTimestamp", null, database.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, database.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, database.LastProcessed.ToString(dateFormat));

writer.WriteAttributeString("LastUpdate", null, database.LastUpdate.ToString(dateFormat));

foreach (Cube cube in database.Cubes)

{

writer.WriteStartElement("Cube");

writer.WriteAttributeString("Name", null, cube.Name);

writer.WriteAttributeString("CreatedTimestamp", null, cube.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, cube.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, cube.LastProcessed.ToString(dateFormat));

writer.WriteEndElement(); // Cube

}

foreach (Dimension dimension in database.Dimensions)

{

writer.WriteStartElement("Dimension");

writer.WriteAttributeString("Name", null, dimension.Name);

writer.WriteAttributeString("CreatedTimestamp", null, dimension.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, dimension.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, dimension.LastProcessed.ToString(dateFormat));

writer.WriteEndElement(); // Dimension

}

writer.WriteEndElement(); // Database

writer.Flush();

}

writer.WriteEndElement(); // Server

writer.Flush();

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

|||

Hello,

I really appreciate the AMO sample code! However, my cubes are currently in 2000, so I would wondering if someone could provide some sample DSO code like above. Output to text file would be good enough.

BTW, to setup automation for the AMO code above, would SSIS be the best place to setup the script? I am thinking either in "Script Component" or "ActiveX" tasks. Let me know if there is a better method.

Sincerely,

-Lawrence

|||

Hi Lawrence,

I strongly recommend you to use the Script Component if you run this code within a SSIS package, because you can use Visual Basic.Net and can use directly the (managed) AMO interface. The ActiveX task is only for backward compatibility reasons. For new code you should use the new ones otherwise you will have migration efforts in the future.

Whether to put it into a SSIS Package or not depends on your requirements and what you want to do with the data you have just gathered in that task. If it is part of your workflow/ETL process, or if you want to use some of the results in other steps/flows, or if you want to use the configuration support of SSIS, or if you want to use the scheduling support with the SQL Server Agent, or, or, or then the answer will be easy (Yes).

Regards,

Bertil

|||

Anyone could provide a sample script of the DSO objects that's related to what I am trying to gather here? I don't think AMO objects would work on 2000 cubes.

Your help is appreciated!

-Lawrence