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! 🙂


Advertisements

3 thoughts on “How To: Generate a CSV file that only contains deltas without SSIS?

  1. You don’t need xp_cmdshell for this (which is potentially a huge security hole).

    You just need to query the capture instance tables (cdc._CT) associated with the source table.

    That’s only if cdc.fn_cdc_get_all_changes_ or cdc.fn_cdc_get_net_changes_ doesn’t meet your needs.

    • Hi Marc,

      Thanks for the comment.

      For this code example, I didn’t want to use SSIS for exporting data. This makes it also usable for SQL Server Express instances and xp_cmdshell is able to export data to CSV. If you know of any other ways to export data without the use of SSIS please let me know.

      In regards to CDC, I thought about that. But, it requires a bit more technical know-how to process such deltas. Perhaps in another example I’ll include that one 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s