SSIS中常用工具语句

truncate table [EDW_PreSource].[dbo].USMD_EMR_IMMUNIZATIONS
delete from [SQLLOGGING].[dbo].[FileMetrics] WHERE PACKAGENAME like '%USMD_EMR_IMMUNIZATIONS%'

-- FILE METRICS
SELECT TOP 10 * FROM [SQLLOGGING].[dbo].[FileMetrics] WHERE PACKAGENAME  
like '%USMD_EMR_IMMUNIZATIONS%' 
	order by ImportDate desc
SELECT TOP 1000 * FROM [EDW_PreSource].[dbo].USMD_EMR_IMMUNIZATIONS


-- SSIS LOG
SELECT * FROM SQLLOGGING.DBO.SSISLOG WHERE PARENTNAME = 'Trucare_Provider_Contract_Deleted_Market_Redesign' 
--and EVENTTYPE='OnError'
ORDER BY SSISLOGID DESC


-- get sql job's running time
SELECT  
	case when PackageDuration  < 60 then convert(varchar(21),PackageDuration) + ' Second(s)'
		when  PackageDuration < 3600 AND  PackageDuration >= 60 
			then convert(varchar(21),CONVERT(DECIMAL(18,2),PackageDuration/60.0 )) + ' Minute(s)'
		when PackageDuration >= 3600 
			then convert(varchar(21),CONVERT(DECIMAL(18,2),PackageDuration/60.0/60.0 )) + ' Hour(s)'
		end AS 'PackageDuration'
FROM 
(
	SELECT MAX(PackageDuration) AS PackageDuration
	FROM SQLLOGGING.DBO.SSISLOG 
	WHERE PARENTNAME = 'TruCare_Provider_Data_Feed'   -- to set sql job here 
	AND convert(varchar(10),PackageStartTime,120) ='2014-08-18' -- to set running date
) A 


 -- ========
-- Control-m job -- devdw02.Ctrlm
select * from CTRLM..CMS_JOBDEF WHERE JOBNAME LIKE '%USMD_EMR_FACILITY%'

-- CONTROL-M
SELECT TOP 1000  * FROM [EM].[dbo].[RUNINFO_HISTORY] 
where sched_table='UHC_LAB'
order by start_time desc

--USMD
--UHC_LAB
-------------------------------------
-- sql job
USE MSDB
GO
SELECT b.[name] [JobName],CASE WHEN b.enabled=1 THEN 'Yes' 
ELSE 'No' END [Enabled] 
,b.description [JobDescription],a.step_name [StepName],a.command [Script] 
FROM msdb.dbo.sysjobsteps a INNER JOIN msdb.dbo.sysjobs b 
ON a.job_id=b.job_id WHERE a.command LIKE '%OPTUM_RX%'

--CONTROL-M JOB
-- OPTUM_RX
select * from CTRLM..CMS_JOBDEF WHERE CMDLINE LIKE '%OPTUM_RX%'

    Private Function ConvertTimeStamp(ByVal FileName As String) As String
        Dim sFileName As String

        sFileName = System.IO.Path.GetFileNameWithoutExtension(FileName) + "_" + _
            DateTime.Now.ToString("yyyyMMdd") + System.IO.Path.GetExtension(FileName)

        Return sFileName
    End Function

    Private Function CheckIsCosmosFile(ByVal FileFullName As String) As Boolean
        FileFullName = System.IO.Path.GetFileName(FileFullName)
        Return Regex.IsMatch(FileFullName, "^WMCOSMOS\d{8}_RXCHF70CL.TXT$")
    End Function