SSIS Active operations

 Just in case I forget about it again:

Stop running SSIS package from SSISDB

 SELECT        

  start_time

, execution_id

, folder_name

, project_name

, package_name

, reference_id

, reference_type

, environment_folder_name

, environment_name

, project_lsn

, executed_as_sid

, executed_as_name

, use32bitruntime

, operation_type

, created_time

, object_type

, object_id

, status

, end_time

, caller_sid

, caller_name

, process_id

, stopped_by_sid

, stopped_by_name

, dump_id

, server_name

, machine_name

, worker_agent_id

, total_physical_memory_kb

, available_physical_memory_kb

, total_page_file_kb

, available_page_file_kb

, cpu_count

, executed_count

FROM           [SSISDB].[catalog].[executions]

WHERE        (end_time IS NULL)

ORDER BY start_time

EXEC [SSISDB].[catalog].stop_operation  @operation_id =54247

Overview of running SSIS package from SSISDB

 Overview of running SSIS package from SSISDB

SELECT        

  start_time
, execution_id
, folder_name
, project_name
, package_name
, reference_id
, reference_type
, environment_folder_name
, environment_name
, project_lsn
, executed_as_sid
, executed_as_name
, se32bitruntime

, operation_type

, created_time

, object_type

, object_id

, status

, end_time

, caller_sid

, caller_name

, process_id

, stopped_by_sid

, stopped_by_name

, dump_id

, server_name

, machine_name

, worker_agent_id

, total_physical_memory_kb

, available_physical_memory_kb

, total_page_file_kb

, available_page_file_kb

, cpu_count

, executed_count

FROM           [SSISDB].[catalog].[executions]

WHERE        (end_time IS NULL)

ORDER BY start_time

number to datetime

convert(datetime ,43040.379150544 )
Till Next Time

image to varchar

convert(varchar(max),convert(varbinary(max),<>))


Till Next Time

MSSQL: Export all packages from MSDB package store

Quick script to generate DTUTIL statements to export all SSIS package from the MSDB package store to the file system using DTUTIL:

;
WITH FOLDERS AS
(
-- Capture root node
SELECT
cast(PF.foldername AS varchar(max)) AS FolderPath
, PF.folderid
, PF.parentfolderid
, PF.foldername
FROM
msdb.dbo.sysssispackagefolders PF
WHERE
PF.parentfolderid IS NULL
-- build recursive hierarchy
UNION ALL
SELECT
cast(F.FolderPath + '\\' + PF.foldername AS varchar(max)) AS FolderPath
, PF.folderid
, PF.parentfolderid
, PF.foldername
FROM
msdb.dbo.sysssispackagefolders PF
INNER JOIN
FOLDERS F
ON F.folderid = PF.parentfolderid
)
, PACKAGES AS
(
-- pull information about stored SSIS packages
SELECT
P.name AS PackageName
, P.id AS PackageId
, P.description as PackageDescription
, P.folderid
, P.packageFormat
, P.packageType
, P.vermajor
, P.verminor
, P.verbuild
, suser_sname(P.ownersid) AS ownername
FROM
msdb.dbo.sysssispackages P
)
SELECT
-- assumes default instance and localhost
-- use serverproperty('servername') and serverproperty('instancename')
-- if you need to really make this generic
'DTUTIL /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\\' + P.PackageName + '" /En file;"F:\\Archief\\SSIS\\'+CONVERT(VARCHAR(50),GETDATE(),112)+'\\' + P.PackageName +'.dtsx";4 /Q' AS cmd
FROM
FOLDERS F
INNER JOIN
PACKAGES P
ON P.folderid = F.folderid
-- uncomment this if you want to filter out the
-- native Data Collector packages
WHERE
F.FolderPath <> '\Data Collector'
and F.FolderPath not Like 'Maintenance%'

Till Next Time

MSSQL Check in which tables a customer appears (FK based)

I made a little script to check in which table a customer 'appeared':


SELECT
  'SELECT ''['+ +SCHEMA_NAME(f.SCHEMA_ID)+'].['+OBJECT_NAME(f.parent_object_id) + ']'' AS TABLE_NAME, COUNT(*) as AANTAL FROM ['+SCHEMA_NAME(f.SCHEMA_ID)+'].['+OBJECT_NAME(f.parent_object_id) +']
WHERE '+ COL_NAME(fc.parent_object_id,fc.parent_column_id) + '= ''<>''
UNION ALL' as [sql],
f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
where OBJECT_NAME (f.referenced_object_id) = '<>'


Till Next Time