Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when executing Report

Hi all,
I have created about 40 reports that access our core ERP solution. All
these reports function perfectly. In the last day I have created 2 new
reports that access a different server/database thus a new shared datasource
was created. However, I am intermittantly receiving the following error
when I attempt to execute the two new reports:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
a.. Cannot create a connection to data source 'Blah2'.
(rsErrorOpeningConnection) Get Online Help
a.. SQL Server does not exist or access denied.
The reports function everytime in Visual Studio. What is going on?
Thanks
ClintSounds like a security issue. What does your datasource specify for a
username/password?
Steve
"AshVsAOD" wrote:
> Hi all,
> I have created about 40 reports that access our core ERP solution. All
> these reports function perfectly. In the last day I have created 2 new
> reports that access a different server/database thus a new shared datasource
> was created. However, I am intermittantly receiving the following error
> when I attempt to execute the two new reports:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> a.. Cannot create a connection to data source 'Blah2'.
> (rsErrorOpeningConnection) Get Online Help
> a.. SQL Server does not exist or access denied.
> The reports function everytime in Visual Studio. What is going on?
> Thanks
> Clint
>
>|||I thought that too. I have changed the datasource to be both shared and
custom. The username and password are correct. I am at a loss..
Especially seeing how it is so intermittant.
"SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> Sounds like a security issue. What does your datasource specify for a
> username/password?
> Steve
> "AshVsAOD" wrote:
> > Hi all,
> >
> > I have created about 40 reports that access our core ERP solution. All
> > these reports function perfectly. In the last day I have created 2 new
> > reports that access a different server/database thus a new shared
datasource
> > was created. However, I am intermittantly receiving the following error
> > when I attempt to execute the two new reports:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
Get
> > Online Help
> > a.. Cannot create a connection to data source 'Blah2'.
> > (rsErrorOpeningConnection) Get Online Help
> > a.. SQL Server does not exist or access denied.
> > The reports function everytime in Visual Studio. What is going on?
> >
> > Thanks
> > Clint
> >
> >
> >|||Not sure whether this will help. When u deploy the shared datsource to the
reportserver, try inputting the connection info and then run the report. I
had the same issue, and the connection trying the dba changed pointed to a
different instance..
Suresh
"AshVsAOD" wrote:
> I thought that too. I have changed the datasource to be both shared and
> custom. The username and password are correct. I am at a loss..
> Especially seeing how it is so intermittant.
> "SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
> news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> > Sounds like a security issue. What does your datasource specify for a
> > username/password?
> >
> > Steve
> >
> > "AshVsAOD" wrote:
> >
> > > Hi all,
> > >
> > > I have created about 40 reports that access our core ERP solution. All
> > > these reports function perfectly. In the last day I have created 2 new
> > > reports that access a different server/database thus a new shared
> datasource
> > > was created. However, I am intermittantly receiving the following error
> > > when I attempt to execute the two new reports:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> Get
> > > Online Help
> > > a.. Cannot create a connection to data source 'Blah2'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > a.. SQL Server does not exist or access denied.
> > > The reports function everytime in Visual Studio. What is going on?
> > >
> > > Thanks
> > > Clint
> > >
> > >
> > >
>
>sql

Error trying to deploy Report

I have a report which works perfectly well within Visual Studio in design. However as soon as I try to deploy this to the Report Server it throws the following error:

w3wp!library!6!02/12/2007-16:29:17:: i INFO: Call to CreateReport( 'Defects by Application (Inactive and Active by Severity) Chart', '/AET Deployment', def)
w3wp!library!6!02/12/2007-16:29:17:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. > System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.DBInterface.CreateObject(Guid id, String shortName, String fullPath, String parentPath, Guid parentId, ItemType objectType, Byte[] objectContent, Guid intermediateSnapshotID, Guid link, String linkPath, ItemProperties objectProperties, String parameters, Byte[] createdBySid, String createdByName, DateTime creationDate, String mimeType)
at Microsoft.ReportingServices.Library.DBInterface.CreateObject(Guid id, String shortName, String fullPath, String parentPath, Guid parentId, ItemType objectType, Byte[] objectContent, Guid intermediateSnapshotID, Guid link, String linkPath, ItemProperties objectProperties, String parameters, String createdBy, DateTime creationDate, String mimeType)
at Microsoft.ReportingServices.Library.CatalogItemCreator.CreateItem()
at Microsoft.ReportingServices.Library.CreateReportAction._CreateReport(String report, String parent, Boolean overwrite, Byte[] definition, Property[] properties)
at Microsoft.ReportingServices.Library.CreateReportAction.CreateReport(Guid batchId, String report, String parent, Boolean overwrite, Byte[] definition, Property[] properties)
End of inner exception stack trace

I cannot identify any issues in the report definition which may indicate where data truncation may occur.

Any help appreciated.

Exception error relates to some problem with the tools, you might try to deploying the report from another machine where BIDS is installed with this project, also confirm the service pack of SQL & RS used here.|||

Using RS 2005 SP1 with SQL 2000 SP3

We have tried deploying this report from a number of locations - from BIDS, from Visual Studio, from RS itself - all fail. We have been able to deploy other reports but cannot see what is causing this deployment to fail ... especially as it runs without issues in preview.

|||

Looks like we have found the issue.

I had been looking at the parameters that were being passed from the Report back to the stored procedures and had verified all of those were OK. However one of the stored procs was returning a dataset with a tinyint which was then being used as an int within other parameters. Not sure how going from tinyint to int can ever result in truncation but when we set the type to int in the stored procedure it deployed OK.