How to use Script Component as Asynchronous Transformation

Posted by on Apr 16, 2010 in Scripting, SSIS | No Comments

Suppose you have a flat file which is not delimited or fixed width or Ragged right. the structure of data is not well formed for Flat File Connection manager to read,  This is an example of data file: date                     : 04/14/2010customer name        : John doeaddress                  : 2014 apple st. Chicago, IT 30000sale order        ...

Read More

Foreach Loop based on Variable – SSIS

Posted by on Mar 23, 2010 in SSIS | No Comments

There are lots of times that you need to set your own enumerator in foreach loop container. For example you want to select special sub directories based on specified conditions and then you want to loop through them. so you need to make your own enumerator. so it’s better to create a variable which contains ...

Read More

Update image column with the physical file with SSIS

Posted by on Feb 26, 2010 in SSIS | No Comments

Problem: you have a table with 2 fields: FilePath -> this field contains address of local image file path FileImage -> this field contains the image exactly, type of this field is image(sql server database) Now you want to update FileImage field with files that are exists in exact local paths. can you do it ...

Read More

Some useful Date expressions for SSIS

Posted by on Feb 22, 2010 in SSIS | No Comments

I engaged in creating some date expressions yesterday, these expressions are very common needed. So I bring them here to share. Suppose your Date Field name is FillDt , 1- number of days in the quarter of date field. For example                     If the fillDt is "2002-03-04" ...

Read More

PIVOT Transformation – SSIS – Complete Tutorial

Posted by on Feb 6, 2010 in SQL Server, SSIS | No Comments

Sometimes, you need to use PIVOT in SSIS. brief explanation of what PIVOT is from msdn is here: PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted ...

Read More

Problem with comma values in Comma Delimited File

Posted by on Jan 22, 2010 in SSIS | No Comments

Assume .csv file which columns delimited by comma ( , ) . and Texts delimited by Double Quote ( " ) like below: "Invoice Number","Invoice Status","Invoice Date of Service","Invoice Date Opened","Invoice Hold from printing/submission","Invoice Branch","Invoice Balances Adjustments","Invoice Balances Payments","Invoice Balances Balance","Patient Last Name","Patient First Name","Patient ID","Policy Payor Name","Invoice Detail Item ID","Invoice Detail Item Name","Invoice Detail ...

Read More

Implement If condition in SSIS package

Posted by on Nov 18, 2009 in SSIS | No Comments

This is common requirement , you need to implement a If condition of If then else condition in a SSIS package flow. The example below illustrated it and implemented it with Precedence Constraints. Problem Scenario: there is a table in sql data source , If any records exist in this table then a Data Flow ...

Read More

How to work with multiple files in FTP Task

Posted by on Nov 11, 2009 in SSIS | No Comments

FTP Task is one of SSIS tasks for File Upload,Download or manage ftp file and folders, FTP Task Configuration is easy and all things that must be done is that create a FTP Connection Manager and then set File Transfer configurations. But problem is when you must do this with more than one file, Answer ...

Read More