• Marek Vavrovic

SQL Server and SSIS Data Types

Updated: Aug 21

Following table list the data type mapping between SQL Server data type and SSIS data type:


SQL Server Data Type SSIS Data Type

tinyint DT_UI1

smallint DT_I2

int DT_I4

bigint DT_I8

real DT_R4

float DT_R8

decimal DT_NUMERIC

numeric DT_NUMERIC

Datetime DT_DBTIMESTAMP

smalldatetime DT_DBTIMESTAMP

Date DT_DATE, DT_DBDATE

bit DT_BOOL

char DT_STR

varchar DT_STR

nchar DT_WSTR

nvarchar DT_WSTR

ntext DT_NTEXT

text DT_TEXT

uniqueidentifier DT_GUID

varbinary DT_BYTES

timestamp DT_BYTES

binary DT_BYTES

xml DT_WSTR

image DT_IMAGE

sql_Variant DT_WSTR

smallmoney DT_CY

money DT_CY



Find the job error message from the SSISDB


USe SSISDB


DECLARE @DATE DATE = GETDATE() - 7 -- This is to restrict the data for last 7 days, used in ON condition


SELECT O.Operation_Id -- Not much of use

,E.Folder_Name AS Project_Name

,E.Project_name AS SSIS_Project_Name

,EM.Package_Name

,CONVERT(DATETIME, O.start_time) AS Start_Time

,CONVERT(DATETIME, O.end_time) AS End_Time

,OM.message as [Error_Message]

,EM.Event_Name

,EM.Message_Source_Name AS Component_Name

,EM.Subcomponent_Name AS Sub_Component_Name

,E.Environment_Name

,CASE E.Use32BitRunTime

WHEN 1

THEN 'Yes'

ELSE 'NO'

END Use32BitRunTime

,EM.Package_Path

,E.Executed_as_name AS Executed_By


FROM [SSISDB].[internal].[operations] AS O

INNER JOIN [SSISDB].[internal].[event_messages] AS EM

ON o.start_time >= @date -- Restrict data by date

AND EM.operation_id = O.operation_id


-- Edit: I change the alias from OMs to OM here:

INNER JOIN [SSISDB].[internal].[operation_messages] AS OM

ON EM.operation_id = OM.operation_id


INNER JOIN [SSISDB].[internal].[executions] AS E

ON OM.Operation_id = E.EXECUTION_ID


WHERE OM.Message_Type = 120 -- 120 means Error

AND EM.event_name = 'OnError'

-- This is something i'm not sure right now but SSIS.Pipeline just adding duplicates so I'm removing it.

AND ISNULL(EM.subcomponent_name, '') <> 'SSIS.Pipeline'

ORDER BY EM.operation_id DESC

2 views0 comments