What Are SSIS Components?
SQL Server Integration Services (SSIS) components streamline extract, transform, and load (ETL) processes and can often eliminate the need to use the Script component in SSIS and writing code altogether.
What Are SSIS Components?
SSIS Components Definition
To understand SSIS components, we first need to review SSIS. SSIS is a platform for building enterprise-level data integration and transformation solutions, and it’s useful for everything from copying files to loading data warehouses. You can also use it to copy files, manage SQL Server objects, and download, cleanse, and analyze data. Essentially, it’s ideal for managing databases and performing data migration at scale.
Unfortunately, using SSIS can be a time-consuming process. People often need to write a Script component in SSIS, which means writing code. This is where SSIS components come in. SSIS components can boost productivity and decrease development time. With the help of SSIS components, data warehousing can become even faster and more flexible.
SSIS Components List
There are many different SSIS components, and SolarWinds® Task Factory® offers over 60 prebuilt SSIS components designed to simplify your life and save you valuable time. Here are the main SSIS components you need to know about.
SSIS SFTP
SSIS Secure File Transfer Protocol (SFTP) brings encrypted security to FTP within SSIS. You can easily send files to or from your SFTP server over secure channels in minutes without writing any code. It’s fast, easy to learn, and convenient.
The SSIS SFTP task in Task Factory works with all SFTP servers as well as implicit SFTP servers, explicit FTPS servers, and HTTP, SOCKS4, SOCKS4a, and SOCKS5 proxies, making it a go-to SFTP component. With theSSIS SFTP task in Task Factory, you can securely upload, download, or delete files from the server; check if a file exists on the server; get a list of files and assign it to a variable; and delete files using a wildcard match. You can even create, upload, download, or delete an entire directory using the SSIS SFTP task.
SSIS Excel Destination
Loading data into Excel isn’t a walk in the park. For one, it’s challenging to control formatting. Moreover, things don’t always work on modern-day machines in 64-bit. In short, exporting from SSIS to Excel can quickly become time-consuming.
Luckily, the Excel SSIS Destination components in Task Factory are compatible with 64-bit machines and capable of writing data using the right data types. With the SSIS Excel Source and Destination components, you can easily read data from Excel and write, insert, append, or overwrite data in Excel. Plus, the Excel SSIS Destination component is an incredible tool for exporting from SSIS to Excel and can even create an Excel table with stylized data.
SSIS Upsert
The Upsert Destination SSIS component makes inserting and updating (or upserting) data much faster. Though the traditional SSIS package follows row-by-row logic—a slow process—The Upsert Destination SSIS component in Task Factory can conditionally update and insert data up to 700 times faster. In fact, the SSIS Upsert component was specifically designed to offer high performance and cut development time.
Configuring the Upsert Destination SSIS component only takes seconds, and it can quickly handle many situations, from updating a variable and logging the number of inserts to searching for row differences. Since the SSIS Upsert component doesn’t use row-by-row operations when updating data and instead uses the high-performance T-SQL MERGE statement to quickly update thousands of rows, many updates normally taking an hour can be completed in less than a minute. The Upsert Destination SSIS component can also perform high-speed bulk updates by checking each column automatically or using a modified date.
SSIS Salesforce
Extracting data from or writing data to Salesforce can be time-consuming, but it doesn’t have to be. The SSIS Salesforce components in Task Factory can help you save time. The SSIS Salesforce components include the Salesforce Source component and the Salesforce Destination component.
The Salesforce Source component is capable of retrieving data from any Salesforce object. Even if you aren’t familiar with salesforce.com, this Task Factory tool can pull Salesforce data into your local environment for in-house processing typically in about five minutes. Configuration is simple and fast, and you’ll be able to retrieve data from custom and built-in salesforce.com objects. Just create a connection manager and select the object you’d like to retrieve data from in the drop-down menu, such as leads, contacts, accounts, and opportunities. For more advanced functionality, you can even customize the Salesforce Object Query Language (SOQL) query and pass variables into the query to retrieve ranges of data.
The Salesforce Destination component helps you write data to your salesforce.com account. Whether you need to send data from your local environment to your Salesforce account or want to delete, update, or upsert data, the Salesforce Destination component can help. This component is easy to use and requires little configuration. Plus, it’s capable of regular and bulk-mode transfer and will allow you to send errors to a different part of your data flow for additional cleansing.
SSIS XML
With the SSIS XML components in Task Factory, creating flexible XML files is easier and faster than ever. These SSIS XML components are powerful and simple-to-use tools.
You can use the XML Output Destination component for SSIS to output your data from one file (or multiple ) into an XML file in a clean format. You can then use an XML schema file (XSD) to format the file to your company’s specifications. Using the XML Destination SSIS component, you can customize column names and export column data as an attribute, element, or element with CDATA or XML data. It’s also worth noting the XML Output Destination component handles parent-child relationships.
There’s also the XML Generator Transform component, which takes data from an SSIS data stream and converts it into an XML representation. This component can take data from one (or multiple) inputs and load everything into an XML file at any destination. Like the XML Output Destination component, the XML Generator Transform component can handle a parent-child relationship.
SSIS REST
You can configure the SSIS REST API components (REST Source and REST Destination) in Task Factory typically in minutes and connect to web and internal RESTful sources. However, each SSIS REST API component has different functionality.
The REST Source component enables you to bring data from almost any web service or cloud application using a REST API into your data flow using SSIS. This component supports data from legacy, XML-based REST endpoints and can also handle data from newly created JSON-based REST endpoints. Plus, the REST Source component supports private and public connections as well as Basic REST, OAuth1, and OAuth2 authentication. With the REST Source component, you can connect to and extract data from platforms like GoToWebinar, Google, Zendesk, MailChimp, Survey Monkey, and more.
With the REST SSIS Destination component, creating records in a RESTful source is much faster. This component has many prebuilt configuration files, so you can quickly start writing data to any internal or web-based RESTful connections. The REST SSIS Destination component supports private and public connections and is equipped with preconfigured options for everything from Zendesk to Google. Whether you need to add people to an email list in MailChimp or create leads in HubSpot, you can use the REST SSIS Destination component to accelerate the process.
Productivity SSIS
If you’re searching for increased productivity and decreased development time, look at the productivity components Task Factory offers. With the help of these components, tedious data warehousing ETL tasks become a breeze. Some popular SSIS productivity components include the following:
- Secure FTP Task: With the Task Factory Secure FTP task, you can bring encrypted security to FTP. This component supports SSH, implicit SFTP, and explicit SFTP as well as HTTP, SOCKS4, SOCKS4a, and SOCKS5 proxies, so you don’t have to stress about the security of your files when you transmit them to your partners—and you won’t have to dedicate any time to coding.
- Email Source Adapter: This component can read emails from IMAP, POP3, and many email providers. With the SSIS Email Source Adapter, you can easily filter messages based on the subject, sender, message to, date received, priority, or body, saving you time in the long run.
- Data Validation Transform: The Data Validation Transform SSIS component can verify your data is clean before insertion. Essentially, it acts as a gatekeeper and can flag suspicious rows, saving you time and preventing serious problems down the line.
- XML Generator Transform: With this component, mapping XML schemas to columns and generating complex XML documents is fast and simple. The XML Generator Transform component also allows you to use source data with a matching XSD file to create XML data, which you can then organize into parent and child elements. It’s fast, convenient, and can greatly increase your productivity.
- Data Flow Nugget Transform: The Data Flow Nugget Transform component can also save you time. This component allows you to set up a package with business or data rules you can save and reuse across multiple SSIS packages to reduce development time. With the Data Flow Nugget Transform component, you won’t have to change the business rules in every SSIS package. Instead, you just need to change the business rules once.
SSIS Dynamics CRM
Connecting to multiple data sources often means writing code using the Script component in SSIS, but this isn’t the case with the Dynamics CRM SSIS components in Task Factory. With these components, you can interact with data stored in your Dynamics CRM server in just a few clicks—no code required.
The Dynamics CRM Source component can quickly retrieve data stored in your Dynamics CRM system. Whether your data is in a hosted online account or on-premises, it’s no problem with the Dynamics CRM Source component. You can even use this component’s advanced filtering capabilities and data preview pane to locate and review the data you need before retrieving it from your Dynamics server.
The Dynamics CRM Destination SSIS component gives you complete control over your Dynamics CRM server data. Need to insert, update, upsert, or delete data stored in your Dynamics CRM Server? This component has you covered. Its user interface is easy to use, so mapping your source data to the Dynamics entity is quick. Within minutes, you can start making changes to your Dynamics data, whether it’s hosted, in Microsoft 365, or in on-premises servers.
SSIS Social Networking
The high-performing SSIS Social Networking components in Task Factory can connect to Facebook, Twitter, and other social media platforms.
The Task Factory Twitter Source component makes extracting data from Twitter simple. Whether you need to view tweets, followers, hashtags, favorited tweets, or retweeted statuses, this component can help. It offers a preview feature, meaning you can ensure your query is correctly configured before running your workflow and save yourself from time-consuming mistakes. Plus, there are plenty of predefined configurations, including built-in pausing compliant with Twitter API call limits, so there’s no need to do any programming. However, you can always use custom API calls if the predefined configurations fall short of your needs.
Similarly, the Facebook Source component in Task Factory makes extracting data from Facebook easier. Whether you need to collect engagement or marketing metrics from Facebook for use in an SSIS package, this component can do the job. You can use predefined configurations, create custom API calls, and verify your query configuration by previewing data, all without needing to write a single line of code.
However, we know there are other social media platforms beyond Twitter and Facebook. This is why Task Factory also offers the REST Source component. With the help of this component, you can easily extract data from nearly every application using a REST API.
SSIS Dimension Merge SCD
The standard Slowly Changing Dimension (SCD) Wizard in SSIS isn’t enough for many, so Task Factory includes a powerful replacement: the Dimension Merge SCD Transform component. Compared to the native SCD Wizard, this component offers a more in-depth interface and doesn’t require the OLE DB Command transformation.
Though built-in components for SSIS traditionally use a row-by-row operation to load data in memory, this component uses the Kimball methodology to load a slowly changing dimension. As a result, users can load a dimension up to 700 times faster than native SSIS SCD components.
The Dimension Merge SCD Transform component is faster and better for scaling than built-in SSIS components. It can handle Type 0, 1, and 2 dimension column types and offers fine-grain customizability for developers and can scale to dimensions with billions of rows.
Fact Table Destination
The Fact Table Destination SSIS component can significantly reduce development time for SSIS packages with multiple dimension lookups. This component helps you load data warehouse fact tables in a fraction of the time, and it allows you to perform ranged lookups, surrogate key lookups in bulk, and data inserts within a single user interface.
It’s also worth noting that you can perform insert and update actions, create column aliases for lookups on the same dimension, define a default value to prevent the addition of NULLS, configure table join conditions to dimension tables from their source data, and batch processes. Essentially, the Fact Table Destination SSIS component simplifies developers’ lives, reducing the required development work and saving them time.
What Are SSIS Components?
SSIS Components Definition
To understand SSIS components, we first need to review SSIS. SSIS is a platform for building enterprise-level data integration and transformation solutions, and it’s useful for everything from copying files to loading data warehouses. You can also use it to copy files, manage SQL Server objects, and download, cleanse, and analyze data. Essentially, it’s ideal for managing databases and performing data migration at scale.
Unfortunately, using SSIS can be a time-consuming process. People often need to write a Script component in SSIS, which means writing code. This is where SSIS components come in. SSIS components can boost productivity and decrease development time. With the help of SSIS components, data warehousing can become even faster and more flexible.
SSIS Components List
There are many different SSIS components, and SolarWinds® Task Factory® offers over 60 prebuilt SSIS components designed to simplify your life and save you valuable time. Here are the main SSIS components you need to know about.
SSIS SFTP
SSIS Secure File Transfer Protocol (SFTP) brings encrypted security to FTP within SSIS. You can easily send files to or from your SFTP server over secure channels in minutes without writing any code. It’s fast, easy to learn, and convenient.
The SSIS SFTP task in Task Factory works with all SFTP servers as well as implicit SFTP servers, explicit FTPS servers, and HTTP, SOCKS4, SOCKS4a, and SOCKS5 proxies, making it a go-to SFTP component. With theSSIS SFTP task in Task Factory, you can securely upload, download, or delete files from the server; check if a file exists on the server; get a list of files and assign it to a variable; and delete files using a wildcard match. You can even create, upload, download, or delete an entire directory using the SSIS SFTP task.
SSIS Excel Destination
Loading data into Excel isn’t a walk in the park. For one, it’s challenging to control formatting. Moreover, things don’t always work on modern-day machines in 64-bit. In short, exporting from SSIS to Excel can quickly become time-consuming.
Luckily, the Excel SSIS Destination components in Task Factory are compatible with 64-bit machines and capable of writing data using the right data types. With the SSIS Excel Source and Destination components, you can easily read data from Excel and write, insert, append, or overwrite data in Excel. Plus, the Excel SSIS Destination component is an incredible tool for exporting from SSIS to Excel and can even create an Excel table with stylized data.
SSIS Upsert
The Upsert Destination SSIS component makes inserting and updating (or upserting) data much faster. Though the traditional SSIS package follows row-by-row logic—a slow process—The Upsert Destination SSIS component in Task Factory can conditionally update and insert data up to 700 times faster. In fact, the SSIS Upsert component was specifically designed to offer high performance and cut development time.
Configuring the Upsert Destination SSIS component only takes seconds, and it can quickly handle many situations, from updating a variable and logging the number of inserts to searching for row differences. Since the SSIS Upsert component doesn’t use row-by-row operations when updating data and instead uses the high-performance T-SQL MERGE statement to quickly update thousands of rows, many updates normally taking an hour can be completed in less than a minute. The Upsert Destination SSIS component can also perform high-speed bulk updates by checking each column automatically or using a modified date.
SSIS Salesforce
Extracting data from or writing data to Salesforce can be time-consuming, but it doesn’t have to be. The SSIS Salesforce components in Task Factory can help you save time. The SSIS Salesforce components include the Salesforce Source component and the Salesforce Destination component.
The Salesforce Source component is capable of retrieving data from any Salesforce object. Even if you aren’t familiar with salesforce.com, this Task Factory tool can pull Salesforce data into your local environment for in-house processing typically in about five minutes. Configuration is simple and fast, and you’ll be able to retrieve data from custom and built-in salesforce.com objects. Just create a connection manager and select the object you’d like to retrieve data from in the drop-down menu, such as leads, contacts, accounts, and opportunities. For more advanced functionality, you can even customize the Salesforce Object Query Language (SOQL) query and pass variables into the query to retrieve ranges of data.
The Salesforce Destination component helps you write data to your salesforce.com account. Whether you need to send data from your local environment to your Salesforce account or want to delete, update, or upsert data, the Salesforce Destination component can help. This component is easy to use and requires little configuration. Plus, it’s capable of regular and bulk-mode transfer and will allow you to send errors to a different part of your data flow for additional cleansing.
SSIS XML
With the SSIS XML components in Task Factory, creating flexible XML files is easier and faster than ever. These SSIS XML components are powerful and simple-to-use tools.
You can use the XML Output Destination component for SSIS to output your data from one file (or multiple ) into an XML file in a clean format. You can then use an XML schema file (XSD) to format the file to your company’s specifications. Using the XML Destination SSIS component, you can customize column names and export column data as an attribute, element, or element with CDATA or XML data. It’s also worth noting the XML Output Destination component handles parent-child relationships.
There’s also the XML Generator Transform component, which takes data from an SSIS data stream and converts it into an XML representation. This component can take data from one (or multiple) inputs and load everything into an XML file at any destination. Like the XML Output Destination component, the XML Generator Transform component can handle a parent-child relationship.
SSIS REST
You can configure the SSIS REST API components (REST Source and REST Destination) in Task Factory typically in minutes and connect to web and internal RESTful sources. However, each SSIS REST API component has different functionality.
The REST Source component enables you to bring data from almost any web service or cloud application using a REST API into your data flow using SSIS. This component supports data from legacy, XML-based REST endpoints and can also handle data from newly created JSON-based REST endpoints. Plus, the REST Source component supports private and public connections as well as Basic REST, OAuth1, and OAuth2 authentication. With the REST Source component, you can connect to and extract data from platforms like GoToWebinar, Google, Zendesk, MailChimp, Survey Monkey, and more.
With the REST SSIS Destination component, creating records in a RESTful source is much faster. This component has many prebuilt configuration files, so you can quickly start writing data to any internal or web-based RESTful connections. The REST SSIS Destination component supports private and public connections and is equipped with preconfigured options for everything from Zendesk to Google. Whether you need to add people to an email list in MailChimp or create leads in HubSpot, you can use the REST SSIS Destination component to accelerate the process.
Productivity SSIS
If you’re searching for increased productivity and decreased development time, look at the productivity components Task Factory offers. With the help of these components, tedious data warehousing ETL tasks become a breeze. Some popular SSIS productivity components include the following:
- Secure FTP Task: With the Task Factory Secure FTP task, you can bring encrypted security to FTP. This component supports SSH, implicit SFTP, and explicit SFTP as well as HTTP, SOCKS4, SOCKS4a, and SOCKS5 proxies, so you don’t have to stress about the security of your files when you transmit them to your partners—and you won’t have to dedicate any time to coding.
- Email Source Adapter: This component can read emails from IMAP, POP3, and many email providers. With the SSIS Email Source Adapter, you can easily filter messages based on the subject, sender, message to, date received, priority, or body, saving you time in the long run.
- Data Validation Transform: The Data Validation Transform SSIS component can verify your data is clean before insertion. Essentially, it acts as a gatekeeper and can flag suspicious rows, saving you time and preventing serious problems down the line.
- XML Generator Transform: With this component, mapping XML schemas to columns and generating complex XML documents is fast and simple. The XML Generator Transform component also allows you to use source data with a matching XSD file to create XML data, which you can then organize into parent and child elements. It’s fast, convenient, and can greatly increase your productivity.
- Data Flow Nugget Transform: The Data Flow Nugget Transform component can also save you time. This component allows you to set up a package with business or data rules you can save and reuse across multiple SSIS packages to reduce development time. With the Data Flow Nugget Transform component, you won’t have to change the business rules in every SSIS package. Instead, you just need to change the business rules once.
SSIS Dynamics CRM
Connecting to multiple data sources often means writing code using the Script component in SSIS, but this isn’t the case with the Dynamics CRM SSIS components in Task Factory. With these components, you can interact with data stored in your Dynamics CRM server in just a few clicks—no code required.
The Dynamics CRM Source component can quickly retrieve data stored in your Dynamics CRM system. Whether your data is in a hosted online account or on-premises, it’s no problem with the Dynamics CRM Source component. You can even use this component’s advanced filtering capabilities and data preview pane to locate and review the data you need before retrieving it from your Dynamics server.
The Dynamics CRM Destination SSIS component gives you complete control over your Dynamics CRM server data. Need to insert, update, upsert, or delete data stored in your Dynamics CRM Server? This component has you covered. Its user interface is easy to use, so mapping your source data to the Dynamics entity is quick. Within minutes, you can start making changes to your Dynamics data, whether it’s hosted, in Microsoft 365, or in on-premises servers.
SSIS Social Networking
The high-performing SSIS Social Networking components in Task Factory can connect to Facebook, Twitter, and other social media platforms.
The Task Factory Twitter Source component makes extracting data from Twitter simple. Whether you need to view tweets, followers, hashtags, favorited tweets, or retweeted statuses, this component can help. It offers a preview feature, meaning you can ensure your query is correctly configured before running your workflow and save yourself from time-consuming mistakes. Plus, there are plenty of predefined configurations, including built-in pausing compliant with Twitter API call limits, so there’s no need to do any programming. However, you can always use custom API calls if the predefined configurations fall short of your needs.
Similarly, the Facebook Source component in Task Factory makes extracting data from Facebook easier. Whether you need to collect engagement or marketing metrics from Facebook for use in an SSIS package, this component can do the job. You can use predefined configurations, create custom API calls, and verify your query configuration by previewing data, all without needing to write a single line of code.
However, we know there are other social media platforms beyond Twitter and Facebook. This is why Task Factory also offers the REST Source component. With the help of this component, you can easily extract data from nearly every application using a REST API.
SSIS Dimension Merge SCD
The standard Slowly Changing Dimension (SCD) Wizard in SSIS isn’t enough for many, so Task Factory includes a powerful replacement: the Dimension Merge SCD Transform component. Compared to the native SCD Wizard, this component offers a more in-depth interface and doesn’t require the OLE DB Command transformation.
Though built-in components for SSIS traditionally use a row-by-row operation to load data in memory, this component uses the Kimball methodology to load a slowly changing dimension. As a result, users can load a dimension up to 700 times faster than native SSIS SCD components.
The Dimension Merge SCD Transform component is faster and better for scaling than built-in SSIS components. It can handle Type 0, 1, and 2 dimension column types and offers fine-grain customizability for developers and can scale to dimensions with billions of rows.
Fact Table Destination
The Fact Table Destination SSIS component can significantly reduce development time for SSIS packages with multiple dimension lookups. This component helps you load data warehouse fact tables in a fraction of the time, and it allows you to perform ranged lookups, surrogate key lookups in bulk, and data inserts within a single user interface.
It’s also worth noting that you can perform insert and update actions, create column aliases for lookups on the same dimension, define a default value to prevent the addition of NULLS, configure table join conditions to dimension tables from their source data, and batch processes. Essentially, the Fact Table Destination SSIS component simplifies developers’ lives, reducing the required development work and saving them time.
Save time managing tedious data warehousing ELT/ETL tasks.
View More Resources
How to Create Your First SSIS Package
A SQL Server Integration Services (SSIS) package includes the necessary components, such as the connection manager, tasks, control flow, data flow, parameters, event handlers, and variables, to execute a specific ETL task. For an introduction to key SSIS functionality, check out the SSIS Basics guide.
View WhitepaperSSIS Basics
This guide walks you through the essential moving parts of SSIS. Check out the next guide in this series that focuses on how to build your first SSIS package.
View WhitepaperMigrating SSIS Packages Into Azure Data Factory
Improve your SQL Server and Azure SQL Database Performance
View Webcast