Thursday, 1 December 2016

SSIS Naming conventions

In 2006 Jamie Thomson came up with naming conventions for SSIS tasks and data flow components. These naming conventions make your packages and logs more readable. Five SQL Server versions and a decade later a couple of tasks and components were deprecated, but there were also a lot of new tasks and components introduced by Microsoft.

Together with Koen Verbeeck (B|T) and AndrĂ© Kamman (B|T) we extended the existing list with almost 40 tasks/components and created a PowerShell Script that should make it easier to check/force the naming conventions. This PowerShell script will soon be published at GitHub as a PowerShell module. But for now you can download and test the fully working proof of concept script. Download both ps1 files and the CSV file. Then open "naming conventions v4.ps1" and change the parameters before executing it. The script works with local packages because you can't read individual package from the catalog, but you can use a powershell script to download your packages from the catalog.
PowerShell Naming Conventions Checker

Task name Prefix Type New
For Loop Container FLC Container
Foreach Loop Container FELC Container
Sequence Container SEQC Container
ActiveX Script AXS Task
Analysis Services Execute DDL Task ASE Task
Analysis Services Processing Task ASP Task
Azure Blob Download Task ADT Task *
Azure Blob Upload Task AUT Task *
Azure HDInsight Create Cluster Task ACCT Task *
Azure HDInsight Delete Cluster Task ACDT Task *
Azure HDInsight Hive Task AHT Task *
Azure HDInsight Pig Task APT Task *
Back Up Database Task BACKUP Task *
Bulk Insert Task BLK Task
CDC Control Task CDC Task *
Check Database Integrity Task CHECKDB Task *
Data Flow Task DFT Task
Data Mining Query Task DMQ Task
Data Profiling Task DPT Task *
Execute Package Task EPT Task
Execute Process Task EPR Task
Execute SQL Server Agent Job Task AGENT Task *
Execute SQL Task SQL Task
Execute T-SQL Statement Task TSQL Task *
Expression Task EXPR Task
File System Task FSYS Task
FTP Task FTP Task
Hadoop File System Task HFSYS Task *
Hadoop Hive Task HIVE Task *
Hadoop Pig Task PIG Task *
History Cleanup Task HISTCT Task *
Maintenance Cleanup Task MAINCT Task *
Message Queue Task MSMQ Task
Notify Operator Task NOT Task *
Rebuild Index Task REBIT Task *
Reorganize Index Task REOIT Task *
Script Task SCR Task
Send Mail Task SMT Task
Shrink Database Task SHRINKDB Task *
Transfer Database Task TDB Task
Transfer Error Messages Task TEM Task
Transfer Jobs Task TJT Task
Transfer Logins Task TLT Task
Transfer Master Stored Procedures Task TSP Task
Transfer SQL Server Objects Task TSO Task
Update Statistics Task STAT Task *
Web Service Task WST Task
WMI Data Reader Task WMID Task
WMI Event Watcher Task WMIE Task
XML Task XML Task
Transformation name Prefix Type New
ADO NET Source ADO_SRC Source *
Azure Blob Source AB_SRC Source *
CDC Source CDC_SRC Source *
DataReader Source DR_SRC Source
Excel Source EX_SRC Source
Flat File Source FF_SRC Source
HDFS File Source HDFS_SRC Source *
OData Source ODATA_SRC Source *
ODBC Source ODBC_SRC Source *
OLE DB Source OLE_SRC Source
Raw File Source RF_SRC Source
SharePoint List Source SPL_SRC Source
XML Source XML_SRC Source
Aggregate AGG Transformation
Audit AUD Transformation
Balanced Data Distributor BDD Transformation *
Cache Transform CCH Transformation *
CDC Splitter CDCS Transformation *
Character Map CHM Transformation
Conditional Split CSPL Transformation
Copy Column CPYC Transformation
Data Conversion DCNV Transformation
Data Mining Query DMQ Transformation
Derived Column DER Transformation
DQS Cleansing DQSC Transformation *
Export Column EXPC Transformation
Fuzzy Grouping FZG Transformation
Fuzzy Lookup FZL Transformation
Import Column IMPC Transformation
Lookup LKP Transformation
Merge MRG Transformation
Merge Join MRGJ Transformation
Multicast MLT Transformation
OLE DB Command CMD Transformation
Percentage Sampling PSMP Transformation
Pivot PVT Transformation
Row Count CNT Transformation
Row Sampling RSMP Transformation
Script Component SCR Transformation
Slowly Changing Dimension SCD Transformation
Sort SRT Transformation
Term Extraction TEX Transformation
Term Lookup TEL Transformation
Union All ALL Transformation
Unpivot UPVT Transformation
ADO NET Destination ADO_DST Destination *
Azure Blob Destination AB_DST Destination *
Data Mining Model Training DMMT_DST Destination
Data Streaming Destination DS_DST Destination *
DataReaderDest DR_DST Destination
Dimension Processing DP_DST Destination
Excel Destination EX_DST Destination
Flat File Destination FF_DST Destination
HDFS File Destination HDFS_DST Destination *
ODBC Destination ODBC_DST Destination *
OLE DB Destination OLE_DST Destination
Partition Processing PP_DST Destination
Raw File Destination RF_DST Destination
Recordset Destination RS_DST Destination
SharePoint List Destination SPL_DST Destination
SQL Server Compact Destination SSC_DST Destination *
SQL Server Destination SS_DST Destination

Example of the prefixes

Tuesday, 25 October 2016

Using PowerShell to create SQL Agent Job for SSIS

I used PowerShell to deploy my SSIS project to the Catalog. Can I also automatically create a SQL Server Agent job with an SSIS jobstep?
SQL Agent Job for SSIS package

Yes, almost every Microsoft product supports PowerShell and SQL Server Agent is no exception. Only the SSIS specific part of the jobstep seems to be a little more difficult to handle. So for this example I first created a SQL Server Agent job(step) for an SSIS package in SSMS manually and then scripted it to see the jobstep command. This command is a long string with all SSIS specific information like the packagepath, enviroment and parameters. Below you see parts of the generated TSQL Script. We are interested in the part behind @command= in row 12:
/****** Object:  Step [PowerShell and SSIS - Master.dtsx]    Script Date: 25-10-2016 22:30:35 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PowerShell and SSIS - Master.dtsx', 
  @os_run_priority=0, @subsystem=N'SSIS', 
  @command=N'/ISSERVER "\"\SSISDB\Finance\PowerShell and SSIS\Master.dtsx\"" /SERVER "\"MyServer\MSSQLSERVER2016\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', 

This command string is used in the PowerShell script below, but hardcoded parts are replaced with values from the PowerShell parameters (see row 66). The rest of the script is more straightforward and easily to adjust or extend. If you're not sure about how to adjust the script then first take a look at the T-SQL script which has similar steps and with the same properties to set.

#PowerShell SSIS JobStep
########## PARAMETERS ##########
# Destination
$SsisServer = "MyServer\MSSQLSERVER2016"
$FolderName = "Finance"
$ProjectName = "PowerShell and SSIS"

# Job
$JobName = "Load DWH"
$MasterPackage = "Master.dtsx"
$JobStartTime = New-TimeSpan -hours 6 -minutes 30

Write-Host "========================================================================================="
Write-Host "==                                 Used parameters                                     =="
Write-Host "========================================================================================="
Write-Host "SSIS Server            : " $SsisServer
Write-Host "FolderName             : " $FolderName
Write-Host "ProjectName            : " $ProjectName
Write-Host "Job name               : " $JobName
Write-Host "MasterPackage          : " $MasterPackage
Write-Host "ScheduleTime           : " $JobStartTime
Write-Host "========================================================================================="
Write-Host ""

# Reference SMO assembly and connect to the SQL Sever Instance 
# Check the number in the path which is different for each version
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($SsisServer) 

# Check if job already exists. Then fail, rename or drop
$SQLJob = $SQLSvr.JobServer.Jobs[$JobName]
if ($SQLJob)
  # Use one of these 3 options to handle existing jobs

  # Fail:
  #Throw [System.Exception] "Job with name '$JobName' already exists."

  # Rename:
  Write-Host "Job with name '$JobName' found, renaming and disabling it"
  $SQLJob.Rename($SQLJob.Name +"_OLD_" + (Get-Date -f MM-dd-yyyy_HH_mm_ss))
  $SQLJob.IsEnabled = $false

  # Drop:
  #Write-Host "Job with name $JobName found, removing it"

#Create new (empty) job 
$SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $SQLSvr.JobServer, $JobName 
$SQLJob.OwnerLoginName = "SA"
Write-Host "Job '$JobName' created"

# Command of jobstep
# This string is copied from T-SQL, by scripting a job(step) in SSMS
# Then replace the hardcode strings with [NAME] to replace them with variables
$Command = @'
$Command = $Command.Replace("[FOLDER]", $FolderName)
$Command = $Command.Replace("[PROJECT]", $ProjectName)
$Command = $Command.Replace("[PACKAGE]", $MasterPackage)
$Command = $Command.Replace("[INSTANCE]", $SsisServer)

# Create new SSIS job step with command from previous block 
$SQLJobStep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "$ProjectName - $MasterPackage" 
$SQLJobStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithSuccess
$SQLJobStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure
$SQLJobStep.SubSystem = "SSIS"
$SQLJobStep.DatabaseName = $SsisServer
$SQLJobStep.Command = $Command
Write-Host "Jobstep $SQLJobStep created"

# Create a daily schedule
$SQLJobSchedule = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Agent.JobSchedule -ArgumentList $SQLJob, "Daily $JobStartTime"
$SQLJobSchedule.IsEnabled = $true
$SQLJobSchedule.FrequencyTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily
$SQLJobSchedule.FrequencyInterval = 1 # Recurs Every Day
$SQLJobSchedule.ActiveStartDate = Get-Date
$SQLJobSchedule.ActiveStartTimeofDay = $JobStartTime
Write-Host "Jobschedule $SQLJobSchedule created"

# Apply to target server which can only be done after the job is created
Write-Host "Job '$JobName' saved"

You could combine this with the deploy script to handle the complete SSIS deployment in one script.
Related Posts Plugin for WordPress, Blogger...