How Does SSIS Load BLObs?

When SSIS loads Binary Large Objects (BLObs), the Data Flow Task first copies the data from the BLOb storage location to some location and stores the BLOb in a file. When it’s time to write the BLOb value, the Data Flow Task reads the BLOb from the file location and passes it to the destination adapter.

Please note: Two of those operations occur in the file system.

“Where in the File System, Andy?”

That’s an excellent question. If you log the Data Flow Task Pipeline Initialization event,

… the log will inform you that, by default, the Data Flow Task will initialize these value to use the locations found in the TEMP and TMP operating system environment variables:

What’s the default value for the TEMP and TMP operation system environment variables? %USERPROFILE%\AppData\Local\Temp. If you have not changed the TEMP and TMP operating system environment variables, you can open that location in Windows Explorer and, when you execute an SSIS package, you can watch the tmp files being created and deleted when you execute a Data Flow Task that loads BLObs:

There’s a problem with this location: it’s on the same drive as the operating system. The operating system is… busy – it’s 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.

How do you change the location? That’s the subject of the next post in this series: BLObs in SSIS – Part 2 – The BLOb-Load Turbo Button.

:{>

 

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