How To: Generate a CSV file that only contains deltas without SSIS?

Hi there, I wrote this particular post for SQL Server Express users that would like to export data once in a while. If you’re running a Standard or Enterprise Edition of SQL Server, I’ll advise you to use SSIS to export data.

Want to export deltas (for BI/DWH purposes with SCD) to CSV files in SQL Server without using SSIS? You might want to check out the SQL code which uses xp_cmdshell .

Important note: Do not use this solution (with xp_cmdshell) on Production environments or other web/internet exposed SQL Server Instances. Xp_cmdshell poses a major security risk, unless it’s setup in a correct matter: How do you secure the SQL Server instance after enabling xp_cmdshell?

Here’s an example of such a file:

The script (downloadable below) creates several database objects:

Stored Procedures:
This SP returns the example dataset.

This SP is generates an example dataset (TOP 1) to validate the code and verify the existince of all SQL Server Database Objects.

This SP actually exports the dataset to a local CSV file (in C:\Temp in this example).

Scalar-valued Functions:
This SvF converts a DATETIMEOFFSET-value into a DATE-value, but in a different format like ‘99991231’ for 9999-12-31.

This SvF converts a DATETIMEOFFSET-value into a TIME-value, but in a different format like ‘123031’ for 12:30:31.

Make sure that xp_cmdshell is enabled:
EXEC master.dbo.sp_configure 'show advanced options', 1
EXEC master.dbo.sp_configure 'xp_cmdshell', 1

Download the T-SQL code here:
Export-deltas-to-CSV-file-SQL (Word document – *.docx)

Have fun! 🙂

How To: Send an automated email on any report subscription failures (SSRS)

Ever wanted to create a process or job that will monitor all report subscriptions (SSRS) for you? It’s your lucky day mate 🙂

Here’s an example of an email that will be send:
(Made it a little bit less wide)


Download the full code here: