As we saw in the first post in this series – BLObs in SSIS – Part 1 – How Does SSIS Load BLObs? – SSIS copies BLObs from their source to the location stored in the TEMP and TMP operation system environment variables. That location defaults to %USERPROFILE%\AppData\Local\Temp.
That’s a problem because that default location is on the same drive as the operating system and the operating system is busy running the server. For the same reason you wouldn’t configure data files on the OS drive, you don’t want to configure Data Flow Tasks to use the OS drive for BLOb temporary storage.
The BLOb-Load “Turbo Button”
Configure the Data Flow Task’s BLOBTempStoragePath property:
Configuring the Data Flow Task’s BLOBTempStoragePath property to anywhere other than the OS drive will improve SSIS Data Flow Task execution performance.
“That’s Great, Andy, But I Have a Bajillion SSIS Packages in Production Already. What About Them?”
You have options.
Option 1: Edit a Bajillion SSIS Packages
Not pretty, I know. But this is a valid option. I don’t recommend this option, but I mention it because it’s a valid solution. I recommend you not hard-code the BLOBTempStoragePath location. Rather, create an SSIS Project or Package Parameter and override that parameter at runtime:
Option 2: Edit a Bajillion SQLAgent Jobs
If SSIS execution is scheduled via SQLAgent SQL Server Integration Services Package job step type, you can persist Property Overrides on the Advanced tab:
To override, set the Property Path to “\Package\<Data Flow Task Name>.Properties[BLOBTempStoragePath]” and the Property Value to the File System Path you wish to use for BLObs transfer.
Similarly, you can edit dtexec command lines to override the BLOBTempStoragePath property of a Data Flow Task:
dtexec /SERVER <SqlServerInstance> /ISSERVER \SSISDB\<CatalogFolder>\<CatalogProject>\<PackageName> /SET "\Package\<Data Flow Task Name>.Properties[BLOBTempStoragePath]";<Path>
Option Three: Use an SSIS Framework
An SSIS Framework that supports SSIS Package Property Overrides – such as the DILM Suite SSIS Framework Commercial and Enterprise Editions – can make changing this property a matter of executing a query to update package execution metadata.
You may be asking, “But Andy, can’t the SSIS Catalog help?” Yes, some. In the next post – BLObs in SSIS – Part 3 – Help from the SSIS Catalog – we will look at ways the SSIS Catalog can help implement these options.
Conclusion
Changing the value of the Data Flow Task BLOBTempStoragePath property can dramatically improve SSIS execution performance. Using an SSIS Framework can help you manage such updates without changing or redeploying your SSIS packages.
:{>
Keep Up: Join my mailing list.
Need Help? Enterprise Data & Analytics delivers SSIS performance tuning and training.
Learn More:
IESSIS1: Immersion Event on Learning SQL Server Integration Services – 2-6 Oct 2017, Chicago
