Compressing files from the SSIS Script Task

I hate SSIS. It seems to me that it is full of certain nuances and unless you are regularly developing SSIS packages, they are easy to forget or it is easy to miss specific important steps. I first started using SSIS back in 2005 when it was directly introduced to replace DTS, but even today I am constantly going around in circles whenever I have to return to write certain functionality.Therefore I have decided to put together a “Bitesize” series of posts that encapsulate simple operations in order to help not just you, but more importantly, remind me! Hopefully, this will save me time in the long run…

Ok, so before I get started I will caveat this quick post by saying that there is an easier (or preferred) way to perform compression on files in SSIS using the ZipFile Class in the recent version of the .NET framework, this is sadly not available if you are using any legacy deployments.

While I have not spent ages adapting the following code to perform what should be a simple thing to do, I have spent enough time to justify sharing what I have done so hopefully you will also find some benefit from the code below too.

    Before you get started, add in the namespaces System.IO and System.IO.Compression into the Namespaces region of your script task script.
    Define the static Compress method which takes a FileInfo type parameter (the FileInfo Class is essentially a wrapper class created from a file path).
    Utilize the Compress method by instantiating a new FileInfo object from a source file name.
#region Namespaces
...
...
using System.IO;
using System.IO.Compression;
#endregion

public static void Compress(FileInfo file)
{
	//Compress file
	using (FileStream originalFileStream = file.OpenRead())
	{
		if (File.GetAttributes(file.FullName) != FileAttributes.Hidden & file.Extension != ".gz")
		{
			using (FileStream compressedFileStream = File.Create(file.FullName + ".gz"))
			{
				using (GZipStream compressionStream = new GZipStream(compressedFileStream,
				   CompressionMode.Compress))
				{
					originalFileStream.CopyTo(compressionStream);
				}
			}
		}
	}
}

public void Main()
{
	// get the source file 
	string inputfile = (string)Dts.Variables["$Project::TemporaryFile"].Value;

	FileInfo file = new FileInfo(inputfile);
	Compress(file);

	Dts.TaskResult = (int)ScriptResults.Success;
}

In the code above I simply use a SSIS Project variable to pass in the required file path to create the compressed archive. Obviously you should modify the code to meet your needs, but his is enough for you to get started.

If you are interested in the reference article that I have adapted (and simplified) to produce this code, then visit How to: Compress Files.

Posted in SQLServerPedia Syndication, SSIS | Tagged | 1 Comment

Why you should attend TUGAIT

TugaIT is an annual event held in Lisbon, Portugal and is in its second year, having first run in 2016. The conference brings together all that is great about the Microsoft Data Platform and beyond, embracing the following areas:

  • Microsoft Data Platform
  • Open-Source Data Platform
  • SharePoint
  • Office 365
  • Enterprise Integration
  • Microsoft Azure (both IaaS and PaaS)
  • Development Methodologies (Agile, Scrum, Kanban, TDD, DDD, …)
  • Programming & Professional Development
  • DevOps

The event is a mammoth 3 days techno-fest consisting of an incredible set of Workshops on Thursday, May 18th and the Friday consists of a 5 track schedule and an awesome full-day workshop with Slava Oks discussing SQL Server on Linux!

After that whirlwind two days of serious content, you are going to need to relax a little more, so the Saturday reverts to a more traditional 8 track agenda -yes, you heard that right!!! 8 Tracks!!!!).

So if my math is correct, I make this 8 workshops and 76 sessions to attend over 3 days -which is clearly going to tick every single box you need it to.

Q. But is it any good?

I was fortunate to attend TUGAIT last year and it was a fantastic experience. The Portuguese Community go out of their way to make you feel welcome, and the venue itself is excellent since it is hosted within the Microsoft Portugal premises. More importantly, there is an amazing selection of speakers from around the world on offer including Microsoft Product managers, MVPs, MCMs and community experts.

For me, the stand out workshop of the lot is “Bringing SQL Server Experience to Linux” by Slava Oks who has been working at Microsoft to make this amazing dream come to reality. I had the pleasure of seeing Slava deliver a fantastic insight into this project during the MVP Summit late last year, and it is, therefore, no surprise to me that TugaIT were keen to get him over to Portugal.

Q. How easy is it to get there?

If you are traveling to Lisbon from outside of Portugal, the journey is about as easy as it can get. From the UK it takes only a couple of hours to get there from any of the major airports (including Stansted) and after landing you can hop straight onto the metro to within walking distance of your hotel and venue. Awesome right?!


In closing, I would strongly recommend that you take the opportunity to attend this event and see all the wonderful speakers on offer. And if you get tired, you can always relax by the river with a glass of wine in hand whilst you recharge your batteries!

Posted in Community, Events, SQLServerPedia Syndication | Tagged | Leave a comment

3 days to SQL Nexus

It is amazing to think that SQL Nexus is just days around the corner after all these months of discussion and preparation by the excellent SQL Nexus team. This year is looking like it will be even better than ever and we are really excited by our pre-conference and main conference speakers and sessions, and if you are coming we hope you enjoy them.

Since today is realistically your last day to book if you are coming via your company purse, I thought it might be helpful to discuss some of my favorite pre-con picks -if you are still struggling to make up your mind.

Microsoft Western Europe Blackbelt (Technical Solution Processional) – Anders Lybecker
This pre-con session title is perhaps a little misleading, and you really have to read through the abstract to see what is being delivered, but when you do, you will see that this is a really interesting IoT session. Most IoT sessions are a bit like a doughnut – a little sickly sweet and with a big hole in the middle, but this session really is “Jam filled!” and with lots of hands-on-labs, streaming analytics and cross platform focus, could well be one of those sessions you look back on and be grateful you attended.

The Complete Primer to SQL Server Virtualization by David Klee
I first met David early last year in Chicago and after a discussion with him about SQL Server virtualization it was clear he was the go to man on this subject (especially in VMware environments). I have heard lots of great feedback about this session from many attendees so it could be one not to miss!

Design & Implement SQL Server HA/DR Hybrid Solutions with Microsoft Azure – Edwin Sarmiento
Edwin’s enthusiasm is infectious and he is perhaps one of the most fastidious and hard working people I know. If you are looking to learn more about HADR in both on-premises and in the Cloud, then you cannot go wrong by attending this session!

Enterprise Data Integration with Biml by Reeves Smith
While every single one of the pre-cons look awesome, I am currently doing a lot of work with SSIS, and therefore Biml is very much on my list of things I need to get good at. I have known Reeves for many years and he is perhaps one of the funniest people I know (as well as being a subject matter expert in Biml), so this is probably my favorite of the bunch for those reasons.

If none of these appeal, you should check out the others, and don’t forget to check out the main agenda which is also super awesome.

Hope to see you there!

Posted in Community, Events, SQLServerPedia Syndication, Uncategorized | Tagged | Leave a comment