Posts Tagged ‘SSIS’

‘Denali’ CTP3 Release

July 18, 2011

On Monday, Microsoft announced the third community technology preview (CTP3) release of its next-generation SQL Server database management system, code-named “Denali”.

I am excited to see some of the enhancements that come out with the first release:

(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/

Daily Extracts… Monthly Reports

April 14, 2010

Have you ever had to run a daily task that feeds a report which is generated at the end of every month?  If your answer is yes, you probably know that this can be either a mundane task to do manually or a logistic nightmare in planning, execution & data contention.  In SSIS, I’ll demonstrate a simple way to accomplish this task while minimizing space requirements and maintenance.  The package will determine the day of the month and make a decision on which portions of the package to execute based on that day.

(more…)

Extract Variable Records to the Same File

January 22, 2010

Hello my faithful reader(s).  I have been away for a while but do not despair:  I am back and 2010 promises some really cool posts.  Today’s post centers on extracting variable record types to a single flat file.  This applies to data warehouse type files that have  header, detail & trailer records somewhat similar to those I have seen for terradata and other warehouse applications.  There will also be a future post in this blog that will describe how to load this data type into your warehouse along with some built in intelligence.

(more…)

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/

Comparing tables with TABLEDIFF

October 30, 2009

Recently, I stumbled across a pretty cool utility called tablediff.  This utility is shipped standard with Microsoft SQL Server and is used to compare the data in two tables for non-convergence (dissimilarities).  The contingencies for this tool require that the tables being compared both have a primary key or a similar field.  This utility can be used from the command prompt or in a batch file which means it can be called from SSIS.   (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…)

Connecting to an XLSX using SSIS

October 16, 2009

You have likely noticed that documents, templates, spreadsheets, and presentations that you create in the 2007 Office release are saved with new file-name extensions with an x at the end of the extension. For example, when you save a spreadsheet in Excel, the file now uses the .xlsx extension, instead of the .xls extension.  In the 2007 Office release, Microsoft has adopted and XML-based file format that is said to improve file compression and provide better integration and interoperability of data.  Unfortunately, SSIS cannot connect to an XLSX file using the Excel Connection Manager. 

(more…)

Dynamically Zipping Files in SSIS

October 13, 2009

There are a number of posts and articles regarding zipping files in SQL Server Integration Services (SSIS). I have found it fairly easy to accomplish this task using an open source zip utility called 7-zip. This utility allows file compression to be executed from the command line, making it very easy to utilize the ‘Execute Process Task’ control flow. 7-zip allows packing and unpacking of ZIP, 7z, GZIP, BZIP2 and TAR and typically provides better compression than WinZip. In this post, I will explain how to dynamically zip files with dynamically defined connection managers and attributes. (more…)


Follow

Get every new post delivered to your Inbox.