SSIS provides a graphical front end to design control flow data processing logic. Once designed, these ‘packages’ are compiled into ‘.dtsx’ packages which can then be distributed and run on any machine with the SSIS tools installed.
Packages contain two main logic flows, a ‘Control Flow’ which defines a sequence of logical operations which are processed in sequence. Each step is completed before the next starts e.g.
1. Empty out work tables in a database
2. Populate the work tables with data
3. Perform calculations and update the values in the work table
4. Update OLAP cubes with the data from the work tables
5. Run reports against the OLAP cubes.
This level of control also allows processing loops to be defined e.g.
For each file in a specified folder, read the contents of the file and write it into a specified table.
The second main logic flow is the ‘Data Flow’. This allows for the processing of data at the record level. Data is read from a ‘Data Source’ and passes down a series of ‘Data Transformations’ to a ‘Data Destination’. These transformations can be as simple as changing the data type of fields e.g. varchar(4000) to varchar(2000) or decimal(18,2) to decimal(8,2), or can be more complex like data merges, joins, pivot tables, multicasts etc. Each transformation is represented by an icon in the designer and the icons are linked together to define the logic path.
Creating SSIS package to transfer data from MS SQL Server 2008 to MySQL
___________________________________________________
Follow the below steps to create a SSIS package for data transfer.
Go to START>MS SQL SERVER 2008>SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO and Click on File>New>Project
Under Business Intelligence Projects, select Integration Services Project and rename the Project Name.
This will open a Package Designer Screen, where you can start designing the data flow for your package.
Configure Connection Managers for you package
___________________________________________________
Right Click in the area where it shows the Connection Manager tab and select New ADO.Net Connection.
You will see the Configuration Manager Editor window
Click New
Now you need to define connection manager configuration for source (i.e. MS SQL Server 2008) in our case.
In the Connection Manager Editor, by default the Provider is set to .Net Providers\SqlClient Data Provider
Use your test server name or IP for Server Name and under connect to database, select the database to use. I’m using ssistest.
Similarly, you need to create connection manager configuration for Destination (i.e. MySQL)
Right Click, under Connection Manager’s tab and Select ADO.NET Connection as mentioned above and click New on Connection Manager Editor.
For Provider click on the drop down arrow to select .Net Providers\odbc data Provider.
In the previous article we had created System DSN name MySQL. We will use the same here for Use user or system data source name.
Enter your login information and Test Connection. It should succeed, and then click Ok.
Now, on Configure ADO.NET Connection Manager screen, you can see both the source and destination are configured.
It’s now time to add control flows to the package, ensure you are on Control Flow tab and Drag Data Flow Task from the Control Flow Items under ToolBox.
Now click on the Data Flow tab above and Drag ADO NET Source and ADO NET Destination as shown below. You can rename the Source and Destination Names from Properties.
Right click on ADO NET SOURCE i.e. MS SQL Server 2008 and click EDIT. Make the below changes as mentioned on the screen.
As mentioned before I have ssistest database which has few sample tables, that I have exported from AdventureWorks database. I’m selecting one of the tables named HumanResources.Department for this example.
In the above screen you can see the test server selected with database and table to transfer the data from.
Similarly right click on MySQL (Destination) and click Edit. On this screen, under ADO.NET Connection Manager select MySQL.root from the drop downlist.
Since we do not have any destination table to map with the source, we need to click on New. It will show you the below message, click OK
Now remove the quotes and Click OK to create a table on destination as shown.
It will come back to the editor,
Click on the Mappings tab and check if all the columns are mapped and click OK.
This will complete the data flow design of our package.
Saving the SSIS Package
___________________________________________________
Once the package is created, save the copy of it to SSIS Package store or MSDB. Follow the below steps,
Click on File> Save Copy of Package.dtsx As from the menu it will open a window,
Select SSIS Package Store as the Package Location and Select the name of your test server.
Enter /File System/Export2MySQL as the package path and Click OK.
Select File > Save Copy of Package.dtsx as again from the menu and Select SQL Server as the Package Location. Select the name of your test server and fill in your authentication information.
Enter Export2MySQL as the package path and Click OK.
Once the copies are saved you can see them under Integration Services Stored Packages as shown below.
Changes on MySQL
___________________________________________________
Before running the Package, make this change on the MySQL Server
mysql> SET GLOBAL sql_mode= ‘ANSI’
Executing the Package
___________________________________________________
You can execute the package either from SSMS and BI Development Studio. I’m doing it from the BI, on the right hand side your BI screen you will find solution explorer, which has the Package.dtsx listed. Right click and select Execute Package.
The moment you click execute the debugging process start and the Output is show below on the BI screen. If the Data Flow Task is changed to Green, it means the package has succeeded, and if it is Red it means somewhere in the flow there was an error.
On the Data Flow Tab, you can see the number of rows transferred in the execution.
There is a progress tab on the screen, where you can check the steps taken in the flow, it also list errors and warnings if any.
Query the MySQL Server to see if the data transfer was successful as show below.
This was about transferring data from MS SQL Server 2008 to MySQL, you can do vice verse by changing the source and destination while designing the package on Data Flow tab
Packages contain two main logic flows, a ‘Control Flow’ which defines a sequence of logical operations which are processed in sequence. Each step is completed before the next starts e.g.
1. Empty out work tables in a database
2. Populate the work tables with data
3. Perform calculations and update the values in the work table
4. Update OLAP cubes with the data from the work tables
5. Run reports against the OLAP cubes.
This level of control also allows processing loops to be defined e.g.
For each file in a specified folder, read the contents of the file and write it into a specified table.
The second main logic flow is the ‘Data Flow’. This allows for the processing of data at the record level. Data is read from a ‘Data Source’ and passes down a series of ‘Data Transformations’ to a ‘Data Destination’. These transformations can be as simple as changing the data type of fields e.g. varchar(4000) to varchar(2000) or decimal(18,2) to decimal(8,2), or can be more complex like data merges, joins, pivot tables, multicasts etc. Each transformation is represented by an icon in the designer and the icons are linked together to define the logic path.
Creating SSIS package to transfer data from MS SQL Server 2008 to MySQL
___________________________________________________
Follow the below steps to create a SSIS package for data transfer.
Go to START>MS SQL SERVER 2008>SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO and Click on File>New>Project
Under Business Intelligence Projects, select Integration Services Project and rename the Project Name.
This will open a Package Designer Screen, where you can start designing the data flow for your package.
Configure Connection Managers for you package
___________________________________________________
Right Click in the area where it shows the Connection Manager tab and select New ADO.Net Connection.
You will see the Configuration Manager Editor window
Click New
Now you need to define connection manager configuration for source (i.e. MS SQL Server 2008) in our case.
In the Connection Manager Editor, by default the Provider is set to .Net Providers\SqlClient Data Provider
Use your test server name or IP for Server Name and under connect to database, select the database to use. I’m using ssistest.
Similarly, you need to create connection manager configuration for Destination (i.e. MySQL)
Right Click, under Connection Manager’s tab and Select ADO.NET Connection as mentioned above and click New on Connection Manager Editor.
For Provider click on the drop down arrow to select .Net Providers\odbc data Provider.
In the previous article we had created System DSN name MySQL. We will use the same here for Use user or system data source name.
Enter your login information and Test Connection. It should succeed, and then click Ok.
Now, on Configure ADO.NET Connection Manager screen, you can see both the source and destination are configured.
It’s now time to add control flows to the package, ensure you are on Control Flow tab and Drag Data Flow Task from the Control Flow Items under ToolBox.
Now click on the Data Flow tab above and Drag ADO NET Source and ADO NET Destination as shown below. You can rename the Source and Destination Names from Properties.
Right click on ADO NET SOURCE i.e. MS SQL Server 2008 and click EDIT. Make the below changes as mentioned on the screen.
As mentioned before I have ssistest database which has few sample tables, that I have exported from AdventureWorks database. I’m selecting one of the tables named HumanResources.Department for this example.
In the above screen you can see the test server selected with database and table to transfer the data from.
Similarly right click on MySQL (Destination) and click Edit. On this screen, under ADO.NET Connection Manager select MySQL.root from the drop downlist.
Since we do not have any destination table to map with the source, we need to click on New. It will show you the below message, click OK
Now remove the quotes and Click OK to create a table on destination as shown.
It will come back to the editor,
Click on the Mappings tab and check if all the columns are mapped and click OK.
This will complete the data flow design of our package.
Saving the SSIS Package
___________________________________________________
Once the package is created, save the copy of it to SSIS Package store or MSDB. Follow the below steps,
Click on File> Save Copy of Package.dtsx As from the menu it will open a window,
Select SSIS Package Store as the Package Location and Select the name of your test server.
Enter /File System/Export2MySQL as the package path and Click OK.
Select File > Save Copy of Package.dtsx as again from the menu and Select SQL Server as the Package Location. Select the name of your test server and fill in your authentication information.
Enter Export2MySQL as the package path and Click OK.
Once the copies are saved you can see them under Integration Services Stored Packages as shown below.
Changes on MySQL
___________________________________________________
Before running the Package, make this change on the MySQL Server
mysql> SET GLOBAL sql_mode= ‘ANSI’
Executing the Package
___________________________________________________
You can execute the package either from SSMS and BI Development Studio. I’m doing it from the BI, on the right hand side your BI screen you will find solution explorer, which has the Package.dtsx listed. Right click and select Execute Package.
The moment you click execute the debugging process start and the Output is show below on the BI screen. If the Data Flow Task is changed to Green, it means the package has succeeded, and if it is Red it means somewhere in the flow there was an error.
On the Data Flow Tab, you can see the number of rows transferred in the execution.
There is a progress tab on the screen, where you can check the steps taken in the flow, it also list errors and warnings if any.
Query the MySQL Server to see if the data transfer was successful as show below.
This was about transferring data from MS SQL Server 2008 to MySQL, you can do vice verse by changing the source and destination while designing the package on Data Flow tab
No comments:
Post a Comment
Hi,
Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.