How to Extract Multiple tables using SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to Extract Multiple tables using SSIS

How to Extract Multiple tables using SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

ranganathanmca

Posted Monday, October 08, 2012 12:12 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 3:52 PM
Points: 42, Visits: 115

Hi,
I want to Extract(Export) 4 tables from SQL SERVER 2008 R2 to a Flat File.
All the four tables are related to each other (through PK & FK relationships).

Tables are
1. Type
2. COA
4. VoucherMaster
5. VoucherDetail

How can i do that ? any help regarding this will be highly appreciated.
I wants to do through SSIS.

Regards,

Post #1369980

Phil Parkin

Posted Monday, October 08, 2012 12:46 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

Four tables with different structures to one file?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1370000

Koen Verbeeck

Posted Wednesday, October 10, 2012 12:15 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364, Visits: 6,462

ranganathanmca (10/8/2012)

We cannot read your mind, so it would be really helpful if you provided us with the following:

* schema of the source tables
* schema of the flat file
* sample input data and desired output

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1370697

churlbut

Posted Thursday, October 11, 2012 3:04 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:36 PM
Points: 975, Visits: 342

I'll take a stab at answering this...

Create a query of the data using SSMS that yields you all the data you need to extract.

Create a Data Flow Task within SSIS

Use OLE DB Source, creating a connection to your Database, for the Data Access Mode choose SQL COMMAND, paste the SQL that was pieced together in SSMS here, click on columns to ensure you have what you need.

Drag a flat File Destination under the OLE DB Source, drag the green available output arrow to the flat file connection.

Edit the flat file connection by right clicking edit, then click on the NEW button, to establish a new connection. Then Click on mappings to map the data between the two.

You might need to create a test file first for setting up the new flat file connection, which can be accomplished real fast by using the import/export wizard out of SSMS. Open up SSMS right click on the database you need to extract data, select Tasks->Export Data and follow the simple wizard to get the data to a flat file.

Once all is done in SSIS click green arrow button (Start Debugging) to execute...

NOTE: if the number of records you want to extract is big, for setting the process up use the TOP command in the select statement of your SQL.

Eat some pop corn and QA the flat file that was generated...

Post #1371795

« Prev Topic | Next Topic »

Permissions