Working with BimlScript to ease and automate your SSIS development

2014-10-01 Leave a comment

Business Intelligence Markup Language (Biml) is a great way to simplify your SSIS development to an easy-to-read XML format. BimlScript expands this even further to allow you to embed .NET code in your Biml to have new Biml dynamically generated with schema changes at your source.  In a recent talk I gave, I discussed what BimlScript can do and gave multiple examples and patterns that you can take and apply to your BI development.

I’ve shared my Slides and Code from the session on my OneDrive at this link:
http://1drv.ms/1vwTMGV

Filed under SSIS

Work-around for 2005 Maintenance Plan bug

2011-03-22 Leave a comment

In working with SQL 2005 Maintenance Plan backup cleanup tasks, a frustrating bug was found. It seems that when the ability to select “hours” as a time criterion was added in SP2, the door was opened for misinterpretation of time units. For example, you can, in your local Management Studio, set the Plan to delete files greater that 4 days old, and when run on the server the Plan will delete files only greater than 4 WEEKS old. This was the full mapping I found:

SET ACTUAL
Hours -> Days
Days -> Weeks
Weeks -> Months
Months -> Years
Years -> Years

I don’t know yet if this is fixed in 2008. For 2005 at least, I suggest refraining from using the built-in “Maintenance Cleanup Tasks” in Maintenance Plans, and instead use a T-SQL Statement task with the snippet below. This will truly delete greater than 4 days. Set your time criteria however you like.

DECLARE @DateOlderThan datetime
SET @DateOlderThan = DATEADD(day,-4,GETDATE())

EXEC master.dbo.xp_delete_file
	 0						-- delete files
	,N'H:\SQLBackup\INST3'	-- full path to the main directory
	,N'bak'					-- file extension
	,@DateOlderThan			-- delete files created before this timestamp
	,1						-- Including first-level sub

Filed under SSIS, SSMS