Tallan's Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Posts Tagged "SSIS"

SSIS FTP Task

 Experience 1 : No ServerPassword? 
The client required a scheduled transfer of multiple TIF files from one server to another over FTP.  Since this task was initially implemented using SSIS, the change seemed to be trivial; I would create a new FTP Connection in the job’s Connection Manager window and replace the current copy task (a Script Task using System.IO functions) with an FTP task inside a For Each Loop to iterate over each file, copying them one by one to the FTP server. 
 
 
 

 
 
 
After a few “trial and error”s due to some changes in the parameters the process ran successfully in debug mode from my local computer.  Happy that I had completed the task, I started the deployment to our QA server so that I could test a scheduled instance of the job.  This is where I ran into a bit…

Column Change Auditing with SSIS

I was recently asked by a client to write an SSIS package to update some data in a database.  One of the requirements was to audit all changes with before and after column values, update user and update date. The client requested that CDC (Change Data Capture) NOT be implemented. And not being very proficient in .net programming languages, I chose not to attempt to code any SSIS “Scripting Component” transformations. An alternate method was chosen that used a combination of SSIS “Conditional Split”, “Derived Column”, “Multicast” and “Union All” transformations. The trick was to “create” a separate audit row for each column that was changed in a database row. The following example shows this method for the row update data flow. The row has already been determined to have column value changes before it reaches this point.

 
 
 
Derive Static Values:                       Used to…

SSIS data management fundamentals

Loading data into a warehouse for use in business intelligence often involves loading data from multiple dissimilar sources. Many times the easiest way to consistently manage the data involves doing extracts from the source systems into a simple text format such as comma delimited. Even legacy systems have some form of reporting engines that allow for text file output, and in dealing with more current database types there are mechanisms such as BCP to allow for dumping output to a text file. A few more reason for using flat files – it allows for a snapshot in time that is replicable, it allows for a single output from a source system to easily be imported into multiple different warehouses, and allows for the imported data to easily be broken into manageable chunks by managing the text files.
SSIS has many features…

The SCD Transformation in SSIS

In this post, we will demonstrate the Slowly Changing Dimension (SCD) transformation that is available for use in a Data Flow task within SQL Server Integration Services (SSIS).  We’ll walk through an example of how to use it, explaining the terms as we go. In addition, I’ll demonstrate some “extra effort” kind of modifications that can be made for a more thorough usage.  After that, we’ll discuss the pros and cons of using it at all.  And we’ll wrap up with some tips on how to tune the transformation for better performance.

\\\