Debugging Errors in SSIS Data Quality Services Cleansing Component
Development Debugging Errors in SSIS Data Quality Services Cleansing Component

As part of Microsoft’s push to include business users in the Business Intelligence space, the addition of Data Quality Services to SQL Server’s feature set opened up the ETL process to the people who, arguably, know the data best. Integration with SSIS was a great move, meaning that this user control extends to automated processes, further closing the gap between data and the business.

However, like most new components, Data Quality Services has some teething problems, and these can be quite hard to find when debugging your SSIS packages. Here’s a quick tip that should help solve some of those tricky to find DQS issues.

read more
Mapping C# DateTime to SQL Server datetime2 via SSIS
Development Mapping C# DateTime to SQL Server datetime2 via SSIS

With SQL Server 2008, Microsoft introduced the new, improved datetime2 format. This newer time storage format is great, because it takes up less storage space, plus you have control over precision and can define your field to the exact specification required. Database columns defined as datetime2 can be mapped in SSIS by using the DT_DBTIMESTAMP2 type. However, in the scenario where you may have a Script Transformation in your SSIS package, and want to assign a .NET DateTime type to a Data Flow column that is mapped to a datetime2 field, you might encounter a DoesNotFitBufferException.

The reason for this is likely down to your specified field precision, and is easily fixed.

read more
Development Optimising SSIS to read from a view using OLE DB Source

I’ve really been neglecting the blog of late and have been taking a bit of a break from a lot of extra curricular business intelligence and data reading. I figured it was about time to get back to posting though, and as luck would have it, my colleague Stephen came to me with an interesting SSIS performance issue that presented the perfect opportunity for a quick blog post. I’ve not written much about SSIS lately, having been drawn off by the shiny sparkle of developments in the self-service BI sector such as Power BI, and playing with Big Data tools like Hadoop. But I still do a lot of work with SSIS and it’s still my go-to large scale ETL tool.

read more
Notes from building a Custom ForEach Enumerator in SSIS
Development Notes from building a Custom ForEach Enumerator in SSIS

I recently posted about a quandary in which I found myself that led to me building my own extended ForEach File Enumerator in SSIS. All things considered, it was a reasonably straightforward experience, with most of my issues stemming from a relative unfamiliarity with Windows Forms development (I was always an ASP.NET man). The whole process can actually be split into four very simple steps to make things easier:

  1. Create your Enumeration function.
  2. Design your UI for SQL Server Data Tools (SSDT).
  3. Validation and assignment of input from the UI.
  4. Deploy your new component.

As long as your new custom component isn ’t too complicated, these steps can be completed very quickly, meaning you can be up and running in only a little longer than it would take to write everything in a Script Task, and think of the re-usability!

read more
Extending the ForEach File Enumerator in SSIS
Development Extending the ForEach File Enumerator in SSIS

One of the primary reasons for using SSIS is to process multiple files, ETL (Extract, Transform, Load) data, and feed it into a destination, for example, a data warehouse. Built-in components such as the For Loop Container and the ForEach Loop Container make it very simple to enumerate a collection and perform some processing tasks for each entry, be it an array, a dataset, or a directory containing files. In fact, the ForEach File Enumerator option of the ForEach Loop Container allows you to even specify a mask to filter the list of files in a directory prior to processing, so you can weed out unwanted files from the loop. But what if a simple string match isn’t powerful enough to perform the filtering you need?

read more

About

picnicerror.net is a personal blog for me to post various ideas, thoughts and discoveries through both my day to day work in marketing technology and general hobbies and interests.

Know More

Social