SQL Server Integration Services – Useful Tips!

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jan 29, 2016

SQL Server Integration Services – Useful Tips!


SQL Server Integration Services (SSIS) uses a variety of connection managers to integrate different data sources into a package allowing for data to be moved around from place to place.

Introduction

SQL Server Integration Services (SSIS) plays a tangible role in data transformation; allowing all kinds of essential data manipulation, both between the databases and within them. Microsoft SQL Server Integration Services, or SSIS, assists you in extracting data from databases, including MySQL. It allows you to load the data into a Microsoft SQL Server database and transform it into a usable format for analysis. This guide will consider common issues that developers encounter while working on SSIS, pulling the data from different sources and related resolutions.

Connection Manager

Configuration of connection handling in SSIS has always been complex. SSIS uses a variety of connection managers to integrate different data sources into a package allowing for data to be moved around from place to place. SSIS is shipped with features to support the secure transmission of data. Although, there are many different data sources, in this blog we will focus specifically on Oracle and MySQL.

One of the most common errors developers’ often encounter is related to “Connectivity via SSIS”. 

Connectivity with Oracle

There are several methods to pull data from Oracle. Here we will discuss some tips to keep in mind while pulling the Data from Oracle using SQL Server Integration Services. To begin with, let’s take a look at the points below: 

  1. Make sure you have adequate permission on the destination server. Verify the ping response using the following command before moving to connectivity:tnsping HostSeverIP
  2. Depending on what provider you choose it can drastically change the speed of the data pulled by multiple folds hence it is one of the most critical and the important areas while planning an ETL. Microsoft OLE DB provider for Oracle, tends to be very fast. It is almost 20 percent faster than other providers.
  3. You want to use an efficient data type as it always ensures the best performance and utilizes minimum resources. A best practice is to use the optimized length for each data type and narrow it as much possible.
  4. Lastly, inserting data in the SQL Server minimizes logged operations to provide the maximum performance. In the data flow task use the fast load options to put data in the target tables. Below is the flow of ETL process.

SSIS

Source: http://blog.aditi.com/data/practices-loading-oracle-data-ssis/

Connectivity with MySQL

Many people may already be aware of how to connect SSIS with MySQL, developers often face SSIS connectivity issues with different data source. MySQL is one of them. Every time we work on varied MySQL version, we come up with varied resolutions. Keep the following points in mind while connecting MySQL with SSIS: 

  1.   Ensure source server has the permission or access to the destination instance. 
  1.   Perform few quick checks with ping or telnet using the following commands.

Telnet Hostserver port

Example: Telnet HostServer 3306 (3306 is default port)

Connector/ODBC 5.1 OR Connector/ODBC 3.51

One of the crucial things to remember is to verify the driver compatibility on the destination server. We often encounter the below errors when SOURCE MySQL version is older or not compatible with the DESTINATION MySQL version. Sounds strange, but ODBC Connector/NET 5.1 or latest versions DO NOT support the old password authentication that leads to the error shown below. 

"Authentication with old password no longer supported, use 4.1 style password”

 SSIS1

In such a case, it’s always recommended to avoid the old password styles being used on the source server. Thus, we can run the below command on the source server to set the OLD password style to false.

SET old_passwords=0;

Once old password style is set to 0, thereafter, we need to set the new password style. If both the servers have got different password styles then we can proceed further, as both the server (Source and Destination) driver versions are different. Therefore, we have to make drivers compatible on the client machine. To perform this, we need to set password of the user@host to the new password style as shown in below command.

SET PASSWORD FOR user@host = PASSWORD('password'); 

NOTE - Make sure you have enough privileges to run the above commands. You can use the below query on your MySQL workbench to verify the access you got for the particular database.

SELECT user,host,password from mysql.user where user='databasename';

The question that comes to mind is that what if a customer does not want to RUN any command on SOURCE server OR what if we haven’t got enough access to view systems tables for a particular database? Well, in such a scenario you need not worry, you can simply install an older version of MySQL ODBC 3.51 Driver on the client machine to resolve the above error.

Unable to connect to any of the specified MySQL hosts

Another problem most developers often face with MySQL is that the specified host “not found”. This is a very common error that we normally get while trying to access MySQL database list from the SSIS. To get rid of this error you just need to verify your permission on the Destination server. If you are a root user or have got admin privileges then you can simply run the flush-hosts using the below command.

Mysql> FLUSH HOST;

SSIS2

Conclusion

To conclude, I would like to reiterate that SSIS supports massive data transformation from different data sources. There have been several enhancements made and features added to the Integration Services with the release of SQL Server 2012. There is considerable evidence that successful data warehousing projects often produce a very high return on investments. I hope you find this information useful. If you have any questions for me then feel free to reach out to me by clicking below: 

Ask Ramandeep