Create Table from text file in SSIS 2008

Well, I have already written few example of how to create flat/text file from SQL Server via SSIS. You can refer those old articles at below links:
This is the time now to go reverse, above examples shows you how to generate text file from SQL Server table but now I am going to show you how to generate SQL Server table from text file.
First generate one text file in your D drive with following data.  Following data would be there in one text file, may be named as “Emp.TXT”. Data is pipe separated; you can have any other separator too like comma.
ritesh|shah|MIS
rajan|shah|account
Alka|Shah|management
Once you have your text file ready, open BIDS (Business Intelligence Development Studio), create new project for SSIS and drag “Data Flow” task from tool bar and drop it to your control area. Once you have dataflow task, double click on it to configure.
Now, important part of this article is coming, before we jump into it, let me tell you, you will need two things in order to copy your text file data to SQL Server.
1.)    As a first thing, you will need source (your text file)
2.)    You will need destination (your SQL Server)
This is just a basic understanding and minimum these much steps needed for copy from any other source to destination.
So, as a first step, we are going to have, “Flat File Source” task in our “Data Flow” tab, double click on it to configure.
In “Flat File Source Editor”, click on “New” to create new source of your text file and name it to “EmpTextFile”.
Browse the text file which we have generated in D drive with name “EMP.TXT”.
We don’t have header information in our text file so table will be created with “Column0”, “Column1” etc., if you want to give meaningful name to your columns,  click on “Advanced” tab in “Flat file connection manager editor” and give meaningful name.  In our case, we will use following name for our columns in “Column” property.
1.)    FirstName
2.)    LastName
3.)    Department
For more details, let us look at below given screen capture:
Click on OK in both the dialog box. And now take “Ado.Net Destination” task from tool box to your control area. Connect green arrow from “Flat File Source” to “Ado.Net destination” task.
Double click on “Ado.Net Destination” task to configure it. To setup database and Server, click on “New” button in “Connection Manager” area.
If you already have a table in your database, select it from drop down list of “use a table or view” , if you don’t already have table in your database, you can create new table by clicking on “New” button. For more details, have a look at screen capture given below.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

LOOKUP Transformation task in SSIS 2008 to copy no match row from source to destination in SQL Server 2008

Yesterday I wrote one of the very simple articles to copy data from source SQL Server to destination at “Transfer data from one instance/database to another instance/database in SQL Server with SSIS”. You can use it, if task needs to be run only one time. If you want to keep running this job daily, weekly or after every certain period of time, you don’t need all data to be copied over again and again. You want to copy only those data which are not exists in destination table.
In order to achieve this task in SSIS 2008, we are going to use “LOOKUP Transformation” task between our source and destination. LOOKUP will do the job of checking source and destination. It can return match/not match data as per your need.
In order to do this job, we will need two dummy database and table in which we perform this exercise. Let us create two data ADV1 and ADV2, or you can use your two databases. Run following TSQL script.
–database 1
USE adv1
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′ union all
SELECT 1,14,’06/07/2010′ union all
SELECT 2,8,’06/08/2010′ union all
SELECT 2,10,’06/09/2010′
GO
–database two
USE adv2
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′
GO
Now, open BIDS and create new project for Integration Services and drop “Data Flow Task” in your work area from toolbox. To configure it, double click on that so that you will be redirected to the “Data Flow” tab.
Now, on dataflow tab, please drag “ADO NET Source” task from toolbox and drop it to your work area.
Double click on it to configure and set your database and table of ADV1 database there, for more information look at below screen shot.
Once your ADO NET Source is ready with configuration, drag “LOOKUP”  transformation task to your work area and join LOOKUP task with ADO NET SOURCE. Now, double click on “LOOKUP” to configure it.
Since, we want to copy only those rows which doesn’t exists in destination table, we have to select “Redirect rows to no match output” option from LOOKUP Transformation Editor and click on  “Connection” tab at left hand side.

 

From the connection tab, select your destination database and table. For more information, please look at the below given screen shot.

 

Now, please understand this concept, “ADO NET Source” returns row from ADV1.ChemicalConsume table which will be “Input” for our “LOOKUP” task. In the connection tab of LOOKUP task, we have made connection to “ADV2.ChemicalConsume” table as a destination so that LOOKUP will compare source and destination.
Now from “LOOKUP Transformation Editor”  click on “Column” tab to set on which columns we are going to make comparison. Our comparison will be on “ChemID” and “ConsumeDate” fields of both the tables. So let us do it.
Now, take “ASP NET Destination” from tool box and join it with green arrow of “LOOKUP” task.
As soon as you will try to join green arrow of “LOOKUP” with “ASP NET Destination” you will get on small dialog box named “INPUT OUTPUT SELECTION”. Set the drop down value as shown in below screen
Now finally double click on “ADO NET Destination”  to configure it and set your ADV2 database along with appropriate table.
Now, run your package by hitting F5 and see output of the package.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah