Quoted_Identifier in SSIS and removing Commas from the source data.

what would you do if you have this situation in the work you do in SSIS.

for example:

I have simple text file which has two columns, first is Name and City.

the file looks like this.

as you can see column city has values which contain Comma as well.

when you build SSIS package to load this type of data to the database, it will load the with the comma to your database.

for example.

when you connect with Flat file source you will get

here you can see the output from the flat file source.

now what should we do to remove this Commas from the values, so we can have the whole values.

what are we having in the middle Derived column transformation.

this is it.

I have two data viewers in my package, to check the values before the change and after the change.

the first data viewer will show the values.

and the second data viewer will show the result, which should be the updated one. without the Commas into the values.

There you go.

A data without the commas into the database.

Enjoy..

How to Debug SSIS Package

What Debug means?

In simple words debugging means we are simply trying to find the errors or problems in our programs.
so in SSIS we try to find problem and error in our package (Task Container).
in SSIS it is a process we perform on DFT, and containers.

so the question is
how do we debug our SSIS package?

so the answer is we can setup a breakup point in a package, a task, a For Each Loop Container, a For Each Loop Container, or a Sequence Container.

To set breakpoints in a package, a task, or a container.

1. open the package. so for this post I have one package called TestPkg.

2. there are two ways to set up break point.

    1. Use F9 button put the cursor debugging.

or you can go with the second option.

    2. To set breakpoints in the package object, click the Control Flow tab, place the cursor anywhere on         the background of the design surface, right -click and then click on Edit Breakpoints.

you can Enable as many required break conditions depending on the requirement you have.

the hit count type and the hit count number for each breakpoints optionally, it is not mandatory.

To set breakpoints in a package control flow, click the Control Flow tab, right click a task, a For Loop Container, a Foreach Loop Container, or a Sequence Container, and then click Edit Breakpoints.

once you set up the break point, you can see the cursor as a red mark on DFT.

the hit count type and the hit count number for each breakpoints optionally, it is not mandatory.

To set breakpoints in an event handler, click the Event Handler tab, right click on a task, or Loop Containers and then click Edit Breakpoints.

Deploying a SSIS Package on Target Machine

we can deploy the SSIS package in in three ways.

The easiest way is File system to deploy the package.
in easy and understandable words, SSIS package actually is XML file, what deployment does is copies SSIS package with the configuration and paste it at given specific place. The place could be anywhere out of three. First is MSSQLSERVER/DTS/Packages, second is in MSDB database, and third is in Package store.

Let’s start with example.
there are three ways to deploy a SSIS package.

1. Deployment utility from SSIS package itself.
2. by using DTUTIL.exe command utility provided by SQL Server.
3. using SSMS (Management Studio)

using SSIS Package deployment Utility

by using this utility Microsoft SSIS technology creates SSIS Package installer in your installation folder.
with using this utility you will get one Manifest file, where your current project is.
the path will be
c:\\yourfolder\projectname\bin\Deployment

let’s see the process.

if the project is not open, open it and select properties of project.

let’s create one test package.

select properties of the package and you will get this page.

as you can see the option CreateDeploymentUtility is set to False.
and we have default path as well, which is bin\Deployment

to deploy our package we have to set the property CreateDeploymentUtility to True.

and Apply it.

once you build your package and run it.
now once you build and run the package you will get the Manifest file along with the package.

my package is stored in this folder

c:\folder\SSIS\TestPkg\

in bin folder you will get Deployment and package itself.

the below image shows the content of Deployment folder which are package itself and one manifest file.

so to perform the deployment you will have to copy and run the Manifest file on to the target machine.
and there you go you package will be in the target machine with configuration file and everything.

————————————————————————————————————————————————————————————————————————

Second way is to use DTUTIL.exe

SQL Server gives an utility which does deployment, but it command line statement.
the utility is DTUTIL, and it can be used only by command prompt.

in the coming example, I will show how to deploy a TestPkg.dtsx package to the file system, package store or SQL Server.

To deploy to the file system, you can use DOS copy command or DTUTIL command

the given example shows the command line to deploy the package to the File System using DTUTIL command.

dtutil / TestPkg.dtsx /Copy File;C\Yourfolder\TestPkg.dtsx

To deploy a package to a Package Store, use this command.

dtutil / TestPkg.dtsx /Copy DTS; TestPkg.dtsx

To deploy a package to a SQL Server, use this command.
this command will deploy the package to your local macine’s default SQL Sever instance.

dtutil / TestPkg.dtsx /Copy SQL;TestPkg.dtsx

if you are deploying your package to different SQL Server instance you need to add this in your command.

/DestinationServer “Servername\yourInstanceName”

————————————————————————————————————————————————————————————————————————

Third way is to use SSMS (Management Studio) itself.

just logon to the Intergration services in SSMS.

remember that the integration services should be running into the system.

so the intial screen you will get is.

so once you enter into the SSMS, you should be getting this below screen.

As you can see in above image, there are two nodes under the Stored Procedure.
File System and MSDB.
File System is the package store with the default location in SQL Server 2008 r2.
the location is C:\Program Files\Microsoft SQL Server\90\DTS\Packages.
MSDB is the MSDB database.

here I wil show the deployment the TestPkg.dtsx package from the location in to the project folder to the package store.

and to the MSDB database.

To Deploy the package to the package store, just right click on the File System and select the option Import Package from the menu.

select package path to get the package.

give the package name and press OK to import the package.

To Deploy the package to the MSDB Database, just right click on the MSDB and select the option Import Package from the menu.

That’s about it..

Good Luck.. Try out Deploy the packages..

Generate Flat file with Month name in it using SSIS Expression.

What you do if some one asks to generate flat file with Month in it.

the format of file we want is FileName_Oct_2012.txt

Now as I showed you in my earlier post, about generating flat file with time stamp.

generating file with time stamp is pretty easy, because we have function and cast operator for it to help us.

but what if we want month name, and year instead of the whole time stamp.

I have already created flat file using time stamp so I will be using my previous post

http://sqlserverandbi.blogspot.com/2012/10/create-dynamic-text-file-with-timestamp.html

and modifying some of the code.

SSIS does not give any particular function to extract month name directly, although it gives MONTH() function to give us the month number. so by using that function we will generate the month name.

let’s start then.

I took one variable which is varMonthName to save month name.

write these expression in the variable expression area.

(month(getdate()) == 1? “Jan”:

month(getdate()) == 2? “Feb”:

month(getdate()) == 3? “Mar”:

month(getdate()) == 4? “Apr”:

month(getdate()) == 5? “May”:

month(getdate()) == 6? “Jun”:

month(getdate()) == 7? “Jul”:

month(getdate()) == 8? “Aug”:

month(getdate()) == 9? “Sep”:

month(getdate()) == 10? “Oct”:

month(getdate()) == 11? “Nov”:

month(getdate()) == 12? “Dec”:””)

Now I am using the created variable varMonthName in my variable strFileName.

and there you go. you will get the file name in this format.
FileName_Oct_2012.txt

try this out..
Good Luck…

Split Flat FIle data and Load into Parent and Child SQL tables

Let’s say we have Denormalized data (coming from a file) that needs to be imported into parent-child tables. 
for example.
the source flat file looks like this.

the file has tab delimiter between the columns.

Now let’s create the parent table and child table to load this source file.

ParentTable


CREATE TABLE [dbo].[ParentTable](
[ParentId] [int] IDENTITY(1,1) NOT NULL,
[Account] [varchar](12) NULL,
[Name] [varchar](50) NULL,
 CONSTRAINT [PK_parentCon] PRIMARY KEY CLUSTERED 
(
[ParentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ChildTable

CREATE TABLE [dbo].[ChildTable](

[ChildId] [int] IDENTITY(1,1) NOT NULL,

[ParentId] [int] NULL,

[Street] [varchar](50) NULL,

[City] [varchar](12) NULL,

[Zip] [varchar](12) NULL,

[Email] [varchar](50) NULL,

 CONSTRAINT [PK_childCon] PRIMARY KEY CLUSTERED 

(

[ChildId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Now we have a DDL statements for ChildTable and ParentTable, let’s design a SSIS package.

SSIS package with name ParentChild.dtsx.

and 

above image shows two Data flow task in Control flow.

one is Parent and another is Child

the connection managers we will be using are given below.

I have already shown the source flat file format.

you can embed into the flat file connection manager by creating flat file with the data.

how we use Flat File connection manager.

After having set up flat file connection manager, let’s go for Data flow task we have put on our Control Flow task.

which are Parent and Child

let’s look at Parent DFT, what we will be having in it.

we will have Flat file source, Sort transformation, and OLE DB destination.

Flat File Transformation Step

1.

2.

Let’s have Steps for Sort Transformation.

1.

Let’s have steps for OLE DB Destination.

1.

2.

let’s look at Child DFT, what we will be having in it.
we will have Flat file source, LookUp transformation, and OLE DB destination.

let’s see what we will be using in Flat File Source Transformation

steps are given below.

1.

2.

we have now source as a flat file.

now let’s take LookUp Transformation.

steps are given below.

1.

2.

3.

now that we have LookUp transformation set it up, let’s have the final transformation which is OLE DB Destination  Transformation to Load Data to Child Table.

while connecting LookUp Transformation to OLE DB Destination transformation use LookUp Match Output as a result from LookUp Transformation to Input for OLE DB Transformation.

let’s have steps here for OLE DB Destination Transformation.

1.

2.

Now we have everything required input and destination table with us.

let’s run the package.

1.

2.

3.

4.

You can see the ParentTable and ChildTable with data.

Good Luck with your Parent and Child table loading.

Create Dynamic Text file with Timestamp with it using SSIS

in my last post I explained how to create text file (flat file) using SSIS.please find given link to generate text file.Create fixed width text file using Flat file Destinationin this post I will show you how to generate flat file with date time stamp.some time we need to generate files according to incoming data.
let’s say we are getting data in every 30 minutes.
and you have to save every file, you can not over write file.
so if you have situation where you can over write file data, you can use getdate functionality with variable and attach it with filename.

but here we will use some another technique to generate file with no over write option.

take Data flow task

Define two variable:
strFile:

scope: Package
Data type: String

set the expression in variable’s property like this.

use this expression

“c:\\Folder\\FileName” + (DT_WSTR, 30)  (DT_DBDATE) GETDATE() + “.txt”

strFileName:

scope: Package
Data type: String

 

Expression is:

“c:\\Folder\\FileName” + (DT_STR,4,1252) DatePart(“yyyy”,getdate()) +
Right(“0” + (DT_STR,4,1252) DatePart(“m”,getdate()),2) +
Right(“0” + (DT_STR,4,1252) DatePart(“d”,getdate()),2) +

“-” +

Right(“0” + (DT_STR,4,1252) DatePart(“hour”,getdate()),2) +
Right(“0” + (DT_STR,4,1252) DatePart(“minute”,getdate()),2) +
Right(“0” + (DT_STR,4,1252) DatePart(“second”,getdate()),2) + “.txt”

and you are all set.
run the package.
and you will the desired result as filename variation.

Good Luck…

Create Fixed Width Text File using Flat File Destination using SSIS

The easiest thing is to create text file using Flat file connection manager.we can load data to text file, but problem is we get data in asymmetric format. which we do not want every time, should be in fixed column size.

so the difficulty arise when we need to create file with fixed width column size.

let’s go through the process. let’s create package called Test.

I suppose you will be having knowledge of TSQL and SQL Server Technologies.

SQL Server Technologies like SSIS (SQL Server Integration Services) After opening Integration Services in
SQL Server Data Tools we will be putting Data flow task to load data into the text file.

Step1:

Step2:

in second step we are taking OLE DB Source, Derived Column, and Flat File destination.

i will explain every transformation with reason.

the first transformation is OLE DB Source to take AdventureWorks2012 database, the table i will use is [HumanResources].[Department]. You can find same table and database with SQL Server version 2008, and 2008R2.

open OLE DB Source editor and select AdventureWorks2012 database and [HumanResources].[Department] table as source.

second transformation is Derived Column. 

as i said before we can load data with only two transformations, one is OLE DB Source and second is Flat File Destination.

but we have different situation here, we just don’t want to load data in asymmetric position.

so we have Derived column as a solution.

in figure we can see that I have created four columns using Add as New column, specifically assigned lengh of output string.

so whenever we get our output it will be in these length formats.

Final Step is to Load data into flat file destination.

we will map the source columns and destination output columns.

the columns we have created using derived columns, we will use them as a source columns.

we will map them with output columns.

and we are set.

run the package, and the file we will be getting as output is given below.

not hoping that, you get the situation like this.

And hope that this blog will help you to resolve the situation.

Good Luck…