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:
[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! 🙂
Clint Huijbers' Blog 