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.

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.