Tuesday 25 October 2016

Using PowerShell to create SQL Agent Job for SSIS

Case
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



















Solution
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', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @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', 
  @database_name=N'MyServer\MSSQLSERVER2016', 
  @flags=0


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

clear
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
  $SQLJob.Alter()

  # Drop:
  #Write-Host "Job with name $JobName found, removing it"
  #$SQLJob.Drop()
}


#Create new (empty) job 
$SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $SQLSvr.JobServer, $JobName 
$SQLJob.OwnerLoginName = "SA"
$SQLJob.Create() 
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 = @'
/ISSERVER "\"\SSISDB\[FOLDER]\[PROJECT]\[PACKAGE]\"" /SERVER "\"[INSTANCE]\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E
'@
$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
$SQLJobStep.Create() 
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
$SQLJobSchedule.Create()
Write-Host "Jobschedule $SQLJobSchedule created"


# Apply to target server which can only be done after the job is created
$SQLJob.ApplyToTargetServer("(local)")
$SQLJob.Alter()
Write-Host "Job '$JobName' saved"


You could combine this with the deploy script to handle the complete SSIS deployment in one script.

Monday 24 October 2016

Using PowerShell for SSIS

Recently I presented an SSIS & PowerShell session at the SQL Serverdays in Schelle, Belgium and SQL Saturday in Utrecht, The Netherlands. You can download the PowerPoints:


And here is a list of some of the scripts used in the demo's:


And some atmospheric impressions:

The venue in Schelle near Antwerp
















Look at me pointing :-)
















The venue in Utrecht






















Look at me again :-)























And some atmospheric impressions on youtube in Dutch

Saturday 22 October 2016

SSIS Appetizer: Cache Transformation File is Raw File

SSIS Appetizer
I'm not sure I have a purpose for this, but did you know that you can use the cache file of the Cache Transformation (introduced in SSIS 2008) as a source file in the Raw File Source.

Demo
For this demo I use two Data Flow Tasks. The first creates the cache file and the second one uses it as a source.
Two Data Flow Task

























1) Create Cache
The first Data Flow has a random source (a flat file in this case) and a Cache Transformation named "CTR - Create Cache"  as a destination. When you create the Cache Connection Manager, make sure to check "Use file cache" to provide a file path for the cache file. Copy the path for the next step.
The Cache Transformation and Connection Manager

















2) Read Cache
The second Data Flow Task uses a Raw File Source. In the editor you can specify the location of the Raw File. Paste the path from the Cache Connection Manager (a .caw file). For demonstration purposes I added a dummy Derived Column behind it with a Data Viewer on the path between them. Now run the package a see the result. You will get some hash columns 'for free'.
Raw File Source













Please let me know in the comments if you found a good purpose for this.

Note: you can't use a raw file as a cache file unless you're able to add the extra hash columns as well.
Related Posts Plugin for WordPress, Blogger...