Extracting Data from a Flat File with SQL Server 2005 Integration Services
 
Published: 06 Jun 2006
Unedited - Community Contributed
Abstract
In this article you will learn how to extract data from a flat file with SQL Server 2005 Integration Services.
by Web Team at ORCS Web
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19498/ 20

What happened to DTS?

SQL Server Integration Services (formerly Data Transformation Services) provides a comprehensive solution for transferring and transforming data between diverse data sources.  The architecture of SSIS has been redesigned to separate package-control flow from data flow.  Two engines have been introduced to handle these aspects of data transformation: the SSIS Run-Time engine and the SSIS Data-Flow engine.

The SSIS Designer is hosted in Business Intelligence Development Studio, making it possible to develop SSIS projects while disconnected from the server.  You can also develop SSIS solutions using SQL Server Management Studio.

Transformations

SSIS provides the following built-in transformations:

·         Conditional Split transformation

·         Multicast transformation

·         Union-All, Merge, and Merge Join transformations

·         Sort transformation

·         Fuzzy Grouping transformation

·         Lookup and Fuzzy Lookup transformations

·         Percentage Sampling and Row Sampling transformations

·         Copy/Map, Data Conversion, and Derived Column transformations

·         Aggregation transformation

·         Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension Processing transformations

·         Pivot and UnPivot transformations

For this example I am importing sample customer contact information from a flat file into the "charlestest" database on a SQL 2005 server.

To create a new Integration Services package to extract data from a flat file:

1.     Choose Start > All Programs > Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio.

2.     On the File menu, choose New > Project to create a new Integration Services project.

3.     In the New Project dialog box, select Integration Services Project.

4.     In the Name box, change the default name to Importing Flat Text File.

5.     Click OK.

Figure 1

ORCS Web, Managed Complex Hosting - Support 

An empty package called Package.dtsx will be created and added to your project.  We can rename this package in the Solution Explorer toolbar; right-click on Package.dtsx and click Rename.  Then rename the default package to importingcustomerdata.dtsx.

Next, we must add a Flat File connection manager to the package that we just created.  A Flat File connection manager allows a package to extract data from a flat file.

To add a Flat File connection manager:

1. Right-click anywhere in the Connection Managers area and then click New Flat File Connection. 

2. On the Flat File Connection Manager Editor screen, for Connection manager name, type Extract Customer Info

3. Click Browse

4. In the Open dialog box, browse to the flat text file you intend to extract data from. 

5. To rename columns in the Flat File connection manager, click Advanced, and in the property pane change the "Name" property for each column.

Figure 2

ORCS Web, Managed Complex Hosting - Support 

Next, we need to add an OLE DB Connection Manager to connect to the destination database.  An OLE DB Connection Manager allows a package to extract data from or upload data into any OLE DB data source (SQL Database for our purposes).  Using the OLE DB Connection Manager, you can specify the server, the authentication method and the default database for the connection.

To add and configure an OLE DB Connection Manager:

1. Right-click in the Connection Managers area and then click New OLE DB Connection.

2. On the Configure OLE DB Connection Manager screen, click New.

3. For Server name, enter SQL05-1.orcsweb.com (or the appropriate server name).

4. In the Log on to the server group, verify that Use SQL Sever Authentication is selected and enter the appropriate username and password. 

5. In the Connect to a database group, choose Select or enter a database name box, and then select "charlestest."

6. Choose Test Connection to verify that the connection settings you have specified are valid.

7. Click OK.

8. Click OK.

9. In the Data Connections pane of the Server Explorer Configure OLE DB Connection Manager screen, verify that sql05-1.orcsweb.com.charlestest is selected.

10. Click OK.

You can also specify "localhost" as the server name and the connection manager will connect to the default instance of local computer.

Figure 3

ORCS Web, Managed Complex Hosting - Support 

Now that we have created the connection managers for the source and destination data, we now need to add a Data Flow task to our package.  The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and provides the functionality for transforming, cleaning, and modifying data as it is moved.  The Data Flow task is where most of the work of an extract, transform and load (ETL) process actually occurs.

To add a Data Flow task:

1. Click the Control Flow tab. 

2. In the Toolbox, expand Control Flow Items and drag a Data Flow Task onto the design surface of the Control Flow tab.

3. On the Control Flow design surface, right-click the newly added Data Flow Task, click Rename, and change the name to Export Customer info.

Figure 4

ORCS Web, Managed Complex Hosting - Support 

Next, you will add and configure a Flat File source for your package.  A Flat File source is a data flow component that uses metadata defined by a Flat File connection manager to specify the format and structure of the data to be extracted from the flat file by a transform process.  The Flat File source can be configured to extract data from a single flat file by using the file format definition provided by the Flat File connection manager.

To add a Flat File Source component:

1. Click on the Data flow tab.  In the Toolbox, expand Data Flow Sources, and then drag a Flat File Source onto the design surface of the Data Flow tab.

2. On the Data Flow design surface, right-click the newly added Flat File Source, click Rename, and change the name to Customer Info*.

3. Double-click the Flat File source to open the Flat File Source Editor box.

4. In the Flat file connection manager box, select Importing Customer Info.

5. Click Columns and verify that the names of the columns are correct.

6. Click OK.

*Alternatively, if the data source file has a header row, it will automatically name the columns for you with the proper names from the header row.

Figure 5

ORCS Web, Managed Complex Hosting - Support 

This package now can extract data from the flat file source and transform that data into a format that is compatible with the destination.  The next task is to actually load the transformed data into the destination.  To load the data, we must add an OLE DB destination to the data flow.  The OLE DB destination can use a database table, view, or an SQL command to load data into a variety of OLE DB-compliant databases.

To add and configure the OLE DB destination:

1. Double-click on OLE DB Destination under DataFlow Destinations in the Toolbox on the right.  Then Drag the green arrow from the Flat file source to the OLE DB Destination as shown below:

Figure 6
ORCS Web, Managed Complex Hosting - Support

2. Then double-click on the OLE DB Destination and in the OLE DB Destination Editor box ensure that sql05-1.orcsweb.com is selected in the OLE DB Connection manager box.

3. In the Name of the table or the view box select the name of the table that you are importing the data into.

Figure 7
ORCS Web, Managed Complex Hosting - Support

4. Click Mappings.

5. Verify that the input columns are mapped correctly to the destination columns.

6. Click OK.

Figure 8
ORCS Web, Managed Complex Hosting - Support

Your package is now complete and should look something like this:

Figure 9

ORCS Web, Managed Complex Hosting - Support

It is time to test your package.

To run this package:

1. Click on the Debug menu and click Start Debugging.

2. After the package has completed running, on the Debug menu click Stop Debugging.

3. The data in your flat text file has now been imported.  Celebrate

References provided by www.microsoft.com

 

Resources

SQLAdvice Forums

SQLAdvice Mailing Lists



User Comments

Title: Reading a value from a flat file to a variable?   
Name: Bruno Pimenta
Date: 2010-01-14 5:36:13 AM
Comment:
I was looking for a simple way to read a value from a flat file into a variable, without using record sets, is this possible?

Thanks,


-----------------------
Satish

You can do this by connecting the flat file task to a script, using an error connection (red arrow) and them making the script return a message, if you have trouble doing this, drop me an email at birpimenta@gmail.com
Title: How to handle Exceptions   
Name: Satish
Date: 2010-01-09 4:11:26 AM
Comment:
Very good exercise. In this exercise i want to add a logic to throw a message when the flat file is not found in the source location. How to handle this. Please provide your answers to satish439@gmail.com
Title: How to handle exceptions   
Name: Satish
Date: 2010-01-09 4:09:38 AM
Comment:
Very good exercise. In this exercise i want to add a logic to throw a message when the flat file is not found in the source location. How to handle this
Title: Flat file source problem   
Name: Emanuele
Date: 2008-06-09 9:57:48 AM
Comment:
hi to all,
I'm working with a Flat File Source in "Fixed Size" modality, so I'd like to check prevently if the file is good formed, i'd like to check that it's a multiple of byte of a single raw..

anyone can help me? thanks
Title: Adding Source Filename Column   
Name: Paul
Date: 2008-06-07 6:14:34 PM
Comment:
Thx for the article. Can you tell us, step-by-step, how to add a column to the destination table that contains the name of the source text file? TIA.
Title: thanks   
Name: Ankit
Date: 2008-03-15 3:49:10 PM
Comment:
Thanks for the nice startup. I was able to transform the theory into practical.

However, i guess u should provide flat file for download, also sometime in the article it was very clear from to right click, etc.

Anyhow, thanks for the nice article.
Title: Naming the file (dynamically)   
Name: Steve
Date: 2007-11-15 1:14:56 PM
Comment:
I'm not sure you can dynamically name it using SSIS GUI, however you could name it programmatically using SSIS advanced functionality.

I recommend you post your questions on http://sqladvice.com/lists/ A lot of MVP's, authors and other SQL experts that can provide assistance.
Title: Naming the file (dynamically)   
Name: Will
Date: 2007-11-15 10:03:50 AM
Comment:
Is there a way one can dynamically name the file using a date stamp (day and year)?

Example: WillsFile20071114
Title: Thousands of Records   
Name: Steve
Date: 2007-11-05 5:37:34 PM
Comment:
SSIS should import the records with no problems. How long it takes could depend on your data structure. If you are running into errors, I recommend you post your questions on http://sqladvice.com/lists/ A lot of MVP's, authors and other SQL gurus that can provide assistance.

Thanks,

Steve
Title: Thousands of Records   
Name: Prem
Date: 2007-11-02 5:16:41 AM
Comment:
HYe peeps,
I want to know how to import flat files into sql server which consisting of thousands of records atleast with some speed

Looking Farward for the response

Regards to all
Title: Fixed Length columns   
Name: Vijay
Date: 2007-09-15 6:21:05 AM
Comment:
HI, It's Fine.. But I want the same thing with fixed length columns. How to do that one. Can you help please... Thanks in advance...
Title: Import   
Name: Steve
Date: 2007-08-28 2:23:18 PM
Comment:
What errors are you getting? Another resource for asking your questions are posted at the bottom of the article.

http://sqladvice.com/lists/ A lot of MVP's, authors and other SQL gurus could also provide assistance.
Title: Flat File text   
Name: Gem
Date: 2007-08-28 11:28:29 AM
Comment:
Hello
data from my flat file contains a / . I would normally put " around the data and set " as the text qualifier ths however is not an option and I cannot import data with a / within it. Any suggestions.
Regards
Title: Not working fro me   
Name: Steve Schofield
Date: 2007-03-15 2:33:17 PM
Comment:
I would verify you are logged into the server with SA or permissions to perform this. Also,check the windows event logs to see if there are any specific errors when you ran this process.
Title: Not working fro me   
Name: SAM
Date: 2007-03-14 9:41:06 PM
Comment:
Hi,
I followed the complete procedure.But in the end it does nothing.Flat file source as wel as Data flow task turns red in the end but no data is transferred actually. Please Help.
Title: Restrict duplicacy   
Name: ss
Date: 2006-08-08 2:15:04 AM
Comment:
Hi;
I am able to import data from a flat source file to sql server,but when I'm executing the packages for number of times it is transferring the same data into the database,can you tell me how to restrict the problem of duplicacy.
Thanks
ss






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 3:46:12 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search