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.
- Export Data From the Database to an Excel File
- Export Data From the Database to Flat File
- Export SQL Table Data to Colon Delimiter Text File
- Export SQL Data to Fixed Width Flat File
- Export Data to Fixed Width with Row Delimiters File
- Export Table Data to Pipe Delimiter Text File
- Export SQL Data to Ragged Right Fixed Width File
- Export Table Data to Semicolon Delimiter Text File
- Export Table Data to Tab Delimiter Text File
- Export Table Data to Flat File with Text Qualifier
- Load Data From Pipe Delimiter File to SQL Server
- Load Data From Tab Delimiter File to SQL Server
- Load Flat File Blank spaces as SQL Server Nulls
- Load Fixed Width Flat File Data to SQL Server
- Load Fixed Width With Row Delimiter File to SQL
- Load Ragged Right Fixed Width File to SQL
- 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.