Query to check Integration failed jobs error message

Query to check Integration failed jobs error message.

Recently i have used SSIS package and deployed this package to one of SSIS server. now i have created a scheduled job to execute this package for time intervals. After some execution job failed. As per our regular terms i have checked job’s history to get error message, but some how its not giving me a correct message.

Error Message from Job History.

Executed as user: DomainName\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:08:00 AM Package execution on IS Server failed. Execution ID: 3403914, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 2:08:00 AM Finished: 2:09:05 AM Elapsed: 64.99 seconds. The package execution failed. The step failed.

Before this deployed version if SSIS, i am  using filesystem package method and if job fails in this filesystem deployment task it shows me the error correctly. Now how can i get the error for deployed package jobs.

Solution : i have created a query to get error message for last failed schedule for particular job.

DECLARE @Full_ssis_command VARCHAR(4000),
 @Job_step_id INT,
 @Package_name VARCHAR(4000),
 @Tablehtml NVARCHAR(MAX),
 @Mailsubject VARCHAR(200),
 @Job_name VARCHAR(100),
 @Job_id UNIQUEIDENTIFIER

SELECT @Job_id = Job_id
FROM MSDB.DBO.Sysjobs
WHERE Name = 'JobName'

SELECT TOP 1 @Job_step_id = Step_id
FROM MSDB.DBO.Sysjobhistory (NOLOCK)
WHERE Run_status <> 1
 AND Step_id > 0
 AND Job_id = @Job_id
ORDER BY Instance_id DESC

SELECT @Full_ssis_command = Command
FROM MSDB.DBO.Sysjobsteps (NOLOCK)
WHERE Job_id = @Job_id
 AND Step_id = @Job_step_id
IF @Full_ssis_command LIKE '%.dtsx%'
 BEGIN
 SELECT @Package_name = RIGHT(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-), Charindex('\', Reverse(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-1)))-1) + '.dtsx''
 END

SELECT [Message_time],
 [Extended_info_id],
 [Package_name],
 [Message_source_name],
 [Subcomponent_name],
 [Package_path],
 [Execution_path],
 LEFT([Message], 400)
FROM SSISDB.[catalog].[Event_messages] (NOLOCK)
WHERE [Package_name] = @Package_name
 AND Event_name = 'OnError'
 AND Message_time >= DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
 AND Operation_id IN (SELECT Max(Operation_id)
 FROM SSISDB.[catalog].[Event_messages](NOLOCK)
 WHERE [Package_name] = @Package_name)
ORDER BY Message_time ASC

Thanks & Regards,
Nirav Gajjar

SQL Server compatibility error in replication

SQL Server compatibility error in replication

From last replication setup on my sandbox , i found wondering error. Error is like this

Error messages:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)
Message: Script failed for Table ‘Caregiver.SpecialAvailability’.
Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0
Source: Microsoft.SqlServer.Smo
Target Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingPreferences)
Message: Column FromDate in object SpecialAvailability contains type Date, which is not supported in the target server version, SQL Server 2005.
Stack: at Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingPreferences options)
at Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingPreferences sp, StringBuilder sb, ColumnCollection columns, ICollection indexes)
at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingPreferences sp, StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired) (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

On the first look i am unable to get the meaning of this error, but i read it twice and i get one line which is useful to identify the error.

Line is.

Message: Column FromDate in object SpecialAvailability contains type Date, which is not supported in the target server version, SQL Server 2005.

This message is showing i am configuring replication on incompatible versions of SQL Server.

I have set database compatibility level same as publisher database and i got resolved.

Thanks & Regards,

Nirav Gajjar