Archive for the ‘SQL’ Category

Converting Rows to Column with Segmentation

February 23, 2012

First of all, my apologies for the long time between posts.  My job (that pays me money) has kept me fairly busy over the past few months and the few that do follow this blog have been asking when I am going to do my next post… Well here it is.

Recently I found myself with the need to convert rows in a table into a single column.   My limitation was that I was only able to pull in 200 rows values per column and those values had to be comma delimited.  Meaning if I had 3118 rows in my source table and I needed to convert those 3118 rows into a single column, but could only have a maximum 200 row values in my column, my output would be 16 rows (3118/200 = 15.59 rounded up to 16).  My first inclination was to pivot the data; but that only allows me to create columns in excess of the values I want to pass into those columns.

(more…)

Advertisements

Shade Under The Cloud

July 20, 2011

More and more companies and consumers are increasingly using use data-intensive applications driven by cloud technology that may have been previously unavailable due to overall cost and complexity.  Now, even mom and pop shops can store data, deploy complex applications using cloud technology.  They are finding that this is often the best way to provide optimal service, reduce failure points, ensure data integrity, reduce operating costs, and create a more easily managed environment.

(more…)

Currency Conversions in SSIS

May 3, 2010

Capturing Real-Time Currency Conversions in SSIS

This article shows how to convert currency using SSIS. A web service task makes an external call to a web service to capture up-to-date exchange rates. The really cool part about this article is that it shows how to configure: web service tasks, recordset destinations and a for-each loop to shred a recordset.

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69768/

Parent-Child records in SSIS

December 1, 2009

Converting a File with Parent-Child Records in SSIS

This article talks about converting a file with Parent-Child records using SSIS.  Parent-child files have a header record (parent) with an undetermined amount of subsequent detail records(child).  The transformation extracts non-blank records, creates a key for each record and splits that record by type and then merges the records back together to create a flattened record with a 1-to-1 relationship. 

Read the article at:

http://www.sqlservercentral.com/articles/SSIS/68409/

Data Quality Part – I

December 1, 2009

Since the blog is named data integrity, I figured I would look up what “data integrity” truly meant.  In searching for the definition on google, data integrity came to be defined as ‘data that is complete or whole. All characteristics of the data including business rules, rules for how pieces of data relate, dates, definitions and lineage must be correct for data to be complete’.  With that in mind, I am going to start a number of posts on Data Quality and Data Profiling over the coming weeks. 

(more…)

Dynamic SQL using CHARINDEX

November 11, 2009

Recently I was called upon to create a new field from an existing field.  The specifications called for the new field to be composed of any data between two dashes.  The dashes could be at any positional value and the data between the dashes was not a consistent length.  

(more…)

Creating Dyanamic Email Content using SSIS

October 27, 2009

From time to time, I will be required to send emails with dynamic content to a number of users within an organization.  Typically, a user will want data in that email that is relevant to them or relevant to their subordinates for reporting/notification purposes.  Thankfully, SSIS can handle this task fairly easily.

(more…)

Thoughts on Error Handling in SSIS

October 2, 2009

I have been thinking about a number of issues that need to be accounted for when developing solutions in SSIS.  Some of them relate to data profiling, but a number  of them relate to SQL tasks, Data Flow and referencing the file system.  Please feel free to add to this list or comment on any of these items.

I will follow up this post with some posts on logging and error handling.

(more…)