Some common Autosys Commands
Some common Autosys Commands
>FS jobname: force start
>KJ jobname : kill job
>SS jobname : set to success
>ONI jobname: On Ice
>OFFI jobname: Off Ice
>ONH jobname:On hold
>OFFH jobname : off hold
>JIL < "path to .jil file name to execute"
>insert_job:jobname
>delete_job:jobname
>update_job:jobname
date_conditions: 1
days_of_week: mo,tu,we,th,fr
start_times: "18:00"
alarm_if_fail: 0
max_run_alarm: 3600
n_retrys: 1
Generating large numbers of partitions using Excel
Link to Chris Webb’s BI Blog page:
http://cwebbbi.wordpress.com/2008/08/27/generating-large-numbers-of-partitions-using-excel/
Read Object variable inside SSIS Script Component
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt, Me.Variables.ObjTradeFeedData)
For Each row In dt.Rows
With Output0Buffer
.AddRow()
.TradeID = row("TradeID").ToString
.BookName = row("TradeBook").ToString
.PortfolioID = row("TradePortfolioID").ToString
.IssuerSDSID = row("UnderlyingSDS").ToString
.CreditRiskCurveId = row("CreditRiskCurveId").ToString
.Currency = row("Currency").ToString
.ValuationDate = row("ValuationDate").ToString
End With
Next
End Sub
End Class
Index fragmentation query – sql server
what is the level of fragmentation on my indexes?
SELECT TOP 50 s.NAME SchemaName
,t.name TABLENAME
,i.name AS TableIndexName
,i.[type_desc]
,p.avg_fragmentation_in_percent
,i.[fill_factor]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) p --'DETAILED'
INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
WHERE p.avg_fragmentation_in_percent > 1
AND s.NAME ='mySchema'
ORDER BY avg_fragmentation_in_percent desc
Increment a Variable Value in SSIS ScriptComponent
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim Counter As Integer
Public Sub New()
Counter = 0
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Counter += 1
Row.NewTenorID = Counter
End Sub
Public Overrides Sub PreExecute()
MyBase.PreExecute()
Counter = Variables.MaxTenorID
End Sub
End Class
Delete records in a batch
WHILE 1 = 1
BEGIN
DELETE TOP(50000) [mySchema].[DimStatic] WITH (XLOCK)
WHERE [PKDimStaticID] NOT IN (SELECT [FKDimStaticID] FROM [#tt])
IF @@rowcount < 1 BREAK;
END
Delete Dupes using CTE
for my own reference:
delete dupe records, keeping one entry for each
;WITH mycte (PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,myrank)
AS
(
SELECT
PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,
RANK() OVER (PARTITION BY PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId
ORDER BY PKFACTMarketDataId) AS myrank
FROM DCRMCube.FACTMarketData
)
DELETE FROM mycte
WHERE MYRANK >1
Process SSAS Measure Groups (Fact tables) using SSIS
for my own reference:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices
Public Class ScriptMain
Public Sub Main()
Dim strFactNames As String = Dts.Variables("FactNames").Value.ToString
Dim strCubeName As String = Dts.Variables("CubeName").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager
oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))
Dts.Events.FireInformation(-1, "Fact Tables to process: ", strFactNames, "", -1, True)
Dts.Log("Fact Tables to process:" + strFactNames, 0, bt)
Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)
Dim oCube As Cube = oDB.Cubes.FindByName(strCubeName)
Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull
If oDB Is Nothing Or oCube Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else
Dim oFact As MeasureGroup
oServer.CaptureXml() = True ' Start capturing XML.
For Each oFact In oCube.MeasureGroups
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If InStr(1, strFactNames, oFact.Name.Replace(" ", "")) > 1 Then
oFact.Process(ProcessType)
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML
' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult
Dts.Log("Processing Facts Now", 0, bt)
Dts.Events.FireInformation(-1, "Process Facts Script: ", "Processing Facts Now", "", -1, True)
oResults = oServer.ExecuteCaptureLog(True, True, True)
Dim oMessage As XmlaMessage
'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Error while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Error while processing facts:", oMessage.Description, "", -1, True)
'The processing failed
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warning while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warning while processing facts:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done
If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
Process SSAS Dimensions using SSIS
for my own reference:
populate DimensionNames var from a exec sql task:
Select CAST(REPLACE(STUFF(
(SELECT ',' + a.tablenamecube AS [text()]
from config.BusinessprocessTables a
WHERE TableType ='D' AND BusinessProcess = 'Metrics' for xml PATH('')),1,1,'' ),' ','') AS VARCHAR(1000)) AS DimensionNames
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices
Public Class ScriptMain
Public Sub Main()
Dim strDimensionNames As String = Dts.Variables("DimensionNames").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager
oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))
Dts.Events.FireInformation(-1, "Dimensions to Process:", strDimensionNames, "", -1, True)
Dts.Log("Dimensions to Process:" + strDimensionNames, 0, bt)
Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)
Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull 'ProcessUpdate
If oDB Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else
Dim oDim As Dimension
oServer.CaptureXml() = True ' Start capturing XML.
For Each oDim In oDB.Dimensions
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If (oDim.MiningModel Is Nothing) Then
If InStr(1, strDimensionNames, oDim.Name.Replace(" ", "")) > 1 Then
oDim.Process(ProcessType)
End If
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML
' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult
Dts.Events.FireInformation(-1, "Process Dimensions script", "Processing Dimensions ow", "", -1, True)
Dts.Log("Processing Dimensions Now", 0, bt)
oResults = oServer.ExecuteCaptureLog(True, True, True)
Dim oMessage As XmlaMessage
'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Errors while processing Dimensions:" + oMessage.Description, 0, bt)
'The processing failed
Dts.Events.FireInformation(-1, "XMLA Errors while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warnings while processing Dimensions:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warnings while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done
If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
SQL SERVER DATABASE LAST RESTORED DATE
use msdb;
select
h.destination_database_name RestoredTo,
h.restore_date RestoreDate,
b.database_name RestoredFrom,
f.physical_name SourceFile,
b.backup_start_date BackupDate
from dbo.RestoreHistory h
inner join dbo.BackupSet b
on h.backup_set_id = b.backup_set_id
inner join dbo.BackupFile f
on f.backup_set_id = b.backup_set_id
order by RestoreDate desc