SSIS Sources and Destinations

This SSIS article will explain the importance of sources and destinations in SQL Server Integration Services and how they facilitate data transfer without writing code. We give a brief introduction about the Important sources and destinations. Then, however, use the hyperlinks to delve deeper into each connection.

You need an appropriate connection manager to establish a connection to work with any of these sources and destinations. Please refer to the Connection Managers article on the SSIS page to understand. 

Introduction to SSIS Sources

SSIS Sources helps to extract data from a wide range of sources so that we can transform them and export them to the required destination. OLE DB Source(Database), Excel sheets, and Flat Files are the most common and essential sources.

Important SSIS Sources

SSIS provides Source Assistance, which provides a wizard to work with common types of data sources. If you are new, try this Source Assistance to extract data from Excel, SQL Server, Flat File, and Oracle.

The following is the list of the most essential and common Sources in Integration Services.

OLE DB Source

OLE DB Source is the most used source in the ETL process. It is the typical source to extract data from all kinds of Databases. It uses OLE DB Connection Manager to establish the connection.

Excel Source

As the name suggests, Excel Source extracts tabular data from Excel spreadsheets.

Flat File Source

Flat Files are simple text and CSV files that hold data where columns separate by delimiter and rows by a new line. Flat File Source uses Flat File Connection Manager to extract data from the local file system.

ADO.NET Source

The ADO.NET Source uses the .Net provider to establish connections and extract data from Databases.

Raw File Source

The Raw File Source is helpful for reading data from the Raw files.

Introduction to SSIS Destinations

SSIS Destinations helps to transfer data from source or transformation to a wide range of destinations. The most common destinations are OLE DB Destination, Flat File, and Excel.

Important SSIS Destinations

SSIS provides Destination Assistance, a wizard to transfer data to common destinations such as Excel, SQL Server, Flat File, and Oracle.

The following is the list of the common destinations in Integration Services.

ADO.NET Destination

The ADO.NET Destination establishes a connection using the .Net provider to load data into Databases.

OLE DB Destination

OLE DB Destination also uses the OLEDB Connection Manager to transfer data to any database. 

Excel Destination

It transfers data to the Excel spreadsheets.

Flat File Destination

FLAT FILE Destination helps to save, transfer, or load data into text or CSV files within the local file system.

Raw File Destination

The Raw File Destination is useful for transferring data to the Raw files.

SSIS Sources and Destinations Tips

  • Always redirect the error rows to a different destination so that you can recheck and validate it.
  • Perform Data type conversions.
  • Always log connection failures.
  • Using SQL Server Authentication to connect with the database is always advisable.
  • Use encryption and masking techniques for sensitive data.

SSIS Basic Import and Export Of Data

The following list includes some of the import and export data operations that will come across in real-time. Remember, all these operations are to showcase the sources and destinations. That’s why we haven’t used any kind of transformation in-between the source and destination.

  1. Export Data From the Database to an Excel File
  2. Export Data From the Database to Flat File
  3. Export SQL Table Data to Colon Delimiter Text File
  4. Export SQL Data to Fixed Width Flat File
  5. Export Data to Fixed Width with Row Delimiters File
  6. Export Table Data to Pipe Delimiter Text File
  7. Export SQL Data to Ragged Right Fixed Width File
  8. Export Table Data to Semicolon Delimiter Text File
  9. Export Table Data to Tab Delimiter Text File
  10. Export Table Data to Flat File with Text Qualifier
  11. Load Data From Pipe Delimiter File to SQL Server
  12. Load Data From Tab Delimiter File to SQL Server
  13. Load Flat File Blank spaces as SQL Server Nulls
  14. Load Fixed Width Flat File Data to SQL Server
  15. Load Fixed Width With Row Delimiter File to SQL
  16. Load Ragged Right Fixed Width File to SQL
  17. Load Multiple Flat Files to SQL

SSIS Import and Export Data Wizard

You can perform the following import and export operations using a Wizard. This wizard might be a starting point for package development.

  1. Export from Database to Flat File using Import & Export Wizard
  2. Import from Flat File to Database using Import Export Wizard
  3. Export Data from DB to Excel using Wizard
  4. Import from Excel to Database using Import Export Wizard