Reza Rad's personal blog

I encounter a question today which was about using a Stored procedure inside a Data flow task, and use result of stored procedure which returned by output parameter beside other columns in Data Stream.

Solution with sample:

suppose this scenario:

you have a source table and a destination table, you want to transfer rows from source table to destination table, so you can use simple data flow with OLE DB source and OLE DB Destination. but problem hits when destination table has few rows inside, and there is Unique Constraint in ID field of Destination table.
in this situation your data flow will fail, because when OLE DB Destination want to insert rows in destination table, it will got Unique Constraint error and it will raise Data Flow error, and this will prevent OLE DB Destination to insert any rows in table, even rows which hasn't equivalent key in destination !

So, a work around needed here to avoid failing package and only move rows which hasn't problem with unique constraint in destination table.

let me explain it with a sample here...

As you may know, there is an enuerator in Foreach Loop Container which help you to loop through files, named File Foreach Enumerator. you can specify a location for searching through files and get files as enuerator in loop.
But, there are some times which you need to loop through specific files not all of files in the directory.
you can use MASK in file names and then just loop through match files. I want to talk about another common real world problem now.

Suppose you have a table which has file names inside, and you have a directory of physical files. now you want to loop through files in directory and check if the file is in table do task1 else do task2.

let's go on sample, through this sample you will find how to implement foreach loop container for file enumerator and use execute sql task with precedence constraint.

I wrote about Validation with XML Task in SSIS here before, now I want to write about XSLT in this post.

XSLT is an acronym of eXtensible Stylesheet Language Transformation. XSLT can transform given XML document to new style.

this is very good way for reading complex XML documents where XML source has week ability to distinguish structure.

let's do a sample with XSLT in XML Task...

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/2010
customer name        : John doe
address                  : 2014 apple st. Chicago, IT 30000
sale order         : 04/15/2010
confirmation tag     : abcdefghi
date                     : 12/14/2010
customer name        : reza rad
address                  : Tehran Iran
sale order         : 02/15/2010
confirmation tag     : rererererere
date                     : 06/14/2009
customer name        : Abolfazl rad goudarzi
address                  : Tehran Iran
sale order         : 02/05/2010
confirmation tag     : fasgfsagas

You need to fetch data and fill them in appropriate fields in a Database table.

As you may know you can not use Flat File manager without any handy works to change data in appropriately manner. in fact you need a Transformation between Flat File Source and the Destination.

But how can you do this?

the answer is ...

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 array of these values and then just loop in the items of this variable.

You know there are few types of enumerators that you can use in foreach loop,

If you want to make your own enumerator based on Variable, you must choose Foreach From Variable Enumerator .

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 with SSIS? How?

Yes,

Use Data Flow Task , Import Column Transformation , OLE DB Command tranformation

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" the count will be 63
                     If the fillDt is "2002-06-20" the count will be 81

expression:

You need to import data from excel file to sql server table, you can do this with a simple import wizard or simple data flow task in SSIS.

But there are lots of times which you want to do this data transfer dynamically.

Suppose these scenarios:

1- you have many excel files with same structure in a folder, you want to transfer data from these files to sql server tables. but you want to use one data flow not a separate data flow for each of them.

2- you want to create a SSIS package that moves data from an excel file to a sql server table , but structure of data can be different in each time of running the package. first time you have an excel file that has 3 columns of data in sheet1, but second time your excel file has more than 10 columns of data.

3- you have many excel files in a directory with different structures, and you want to import them all in separate tables in sql server. making a Data Flow for each of them in SSIS is very time consuming, you need to find a generic way for data flow.

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 in the final output

This tutorial provides a complete pivot sample on AdventureWorks sample SQL Server database with SSIS 2008...

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 Balance","Invoice Detail Qty","Invoice Detail Item Group"
"12345","Open","3/31/2009 12:00:00 AM","5/26/2009 8:09:02 AM","False","Test","($346.23)","$0.00","$346.23","Smith","John","12344","[Patient]","Test-1234","5""x8"" Multi-Axis Stump Support, Therafin, 31552","$51.12","1","Wheelchair Accessories"

If you look at "5""x8"" Multi-Axis Stump Support, Therafin, 31552" , there are some ( " ) and ( , ) inside this value.

So, How it must be handled in SSIS Flat File Source Connection Manager?

If it's the first time that you want to host a WCF web site under IIS7, then you probably got this error:

HTTP Error 404.17 - Not Found

Solution:

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 must transfer this data, Else a process must start.

Solution:

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 is using file mask ...