Reza Rad's personal blog

Output Parameter of Stored Procedure In OLE DB Command - SSIS

nospam@example.com (Reza Rad) — Tue, 17 Aug 2010 17:07:42 -0500

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:

Continue reading "Output Parameter of Stored Procedure In OLE DB Command - SSIS"

How to handle Failed Rows in a Data Flow

nospam@example.com (Reza Rad) — Fri, 16 Jul 2010 02:22:59 -0500

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...

Continue reading "How to handle Failed Rows in a Data Flow"

Looping through specific files - SSIS

nospam@example.com (Reza Rad) — Tue, 06 Jul 2010 10:31:48 -0500

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.

Continue reading "Looping through specific files - SSIS"

XML Task - Changing Style of Data - XSLT

nospam@example.com (Reza Rad) — Sat, 05 Jun 2010 00:35:35 -0500

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...

Continue reading "XML Task - Changing Style of Data - XSLT"

XML Task - Validating Data

nospam@example.com (Reza Rad) — Sat, 29 May 2010 13:40:31 -0500

I decided to explain XML Task for a while. This task has many abilities to work with XML.
to use this Task, create a SSIS package , add a XML task ,
double click on it, Xml Task Editor window will open.
the most important property of XML task is : OperationType .
there are 6 OperationTypes in SSIS 2008:
-Validate
-XSLT
-XPATH
-Merge
-Diff
-Patch
there operation types has great abilities on xml files. in this post I will talk about Validate operation type...

Continue reading "XML Task - Validating Data"

How to use Script Component as Asynchronous Transformation

nospam@example.com (Reza Rad) — Fri, 16 Apr 2010 14:54:32 -0500

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 ...

Continue reading "How to use Script Component as Asynchronous Transformation"

Foreach Loop based on Variable - SSIS

nospam@example.com (Reza Rad) — Tue, 23 Mar 2010 14:41:31 -0500

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 .

Continue reading "Foreach Loop based on Variable - SSIS"

Update image column with the physical file with SSIS

nospam@example.com (Reza Rad) — Fri, 26 Feb 2010 11:42:46 -0600

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

Continue reading "Update image column with the physical file with SSIS"

Some useful Date expressions for SSIS

nospam@example.com (Reza Rad) — Mon, 22 Feb 2010 10:57:02 -0600

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:

DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1"),fillDt)

Continue reading "Some useful Date expressions for SSIS"

Excel to SQL Server, Dynamic Data Flow

nospam@example.com (Reza Rad) — Sat, 20 Feb 2010 12:18:54 -0600

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.

Continue reading "Excel to SQL Server, Dynamic Data Flow"

PIVOT Transformation - SSIS - Complete Tutorial

nospam@example.com (Reza Rad) — Sat, 06 Feb 2010 16:08:18 -0600

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...

Continue reading "PIVOT Transformation - SSIS - Complete Tutorial"

Problem with comma values in Comma Delimited File

nospam@example.com (Reza Rad) — Fri, 22 Jan 2010 12:00:18 -0600

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?

Continue reading "Problem with comma values in Comma Delimited File"

Hosting WCF in IIS7

nospam@example.com (Reza Rad) — Fri, 11 Dec 2009 13:53:41 -0600

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:

Continue reading "Hosting WCF in IIS7"

Implement If condition in SSIS package

nospam@example.com (Reza Rad) — Thu, 19 Nov 2009 00:18:27 -0600

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:

Continue reading "Implement If condition in SSIS package"

How to work with multiple files in FTP Task

nospam@example.com (Reza Rad) — Thu, 12 Nov 2009 00:14:33 -0600

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 ...

Continue reading "How to work with multiple files in FTP Task"