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:
Deltas_ExampleDataset-20130727113829

The script (downloadable below) creates several database objects:
Export-deltas-to-CSV-file-SQL-Server-database-objects

Stored Procedures:
[dbo].[usp_GenerateDeltas_ExampleDataset]
This SP returns the example dataset.

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

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


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

[dbo].[fn_DateTimeOffsetToTimeId]
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
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE



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

Have fun! 🙂


(Re-) Naming Excel worksheets for SSRS export

A good thing to know is that it’s very simple to (re-) name the Excel worksheets.
It’s a bit funny, but it’s actually the property PageName that does the trick:

Ta-da!

This is the most ridicules post ever, but I really didn’t know this and had to google it 😛
Just sharing it with you guys!