This tutorial gives you the bare minimum information to copy data from an Oracle database to a MS SQL Server database using SSIS (Integration Services).
Edit tnsnames.ora
Create an entry in tnsnames.ora file. This is a file that Oracle uses to abstract the server, port, etc and make it easy to reference. This file resides on the machine that SSIS is running from. The changes will also need to be done on the server were SSIS runs. If you don’t have Oracle installed on that server, you need to do that also.
Here is an example that we will use in this example:
TEMPTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = serverNameHere)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLETEST)
)
)
You can make sure you have this configured properly by opening up a command prompt and typing
sqlplus username/password@TEMPTEST
If you get a SQL> prompt then you have configured the tnsnames.org entry ok.
Create a project
In Business Intelligence Studio or Visual Studio if you have it, create a new Business Intelligence project.
Add an Oracle Connection
There are two types. One that is from Microsoft and the other that is from Oracle. Either one will technically work.
MS OLE DB Provider for Oracle
This requires an entry in the tnsnames.ora file that matches the name of the Server you enter in the configuration dialog for the OLE DB datasource.
Click the Test Connection to verify it works.
Oracle Provider for OLE DB
This requires an entry in tnsnames.ora file that matches the Server you enter in the configuration dialog for the OLE DB datasource.
Click the Test Connection to verify it works.
Add MS SQL Server Connection
There are like Oracle several options for connecting to a MS SQL Server database. In this example, I am going to use OLE DB. Right-Click the Connection Managers area and choose New ADO.NET connection. In this case we are connecting to a named instance called SQL2005. If you are using the default instance of SQL Server, then just remove the \SQL2005 from this example. The name of the database is called TestSSISDB. The user is also called TestSSISDB.
Add Data Flow Task
Drag the Data Flow Task onto your Control Flow tab / page. Rename the task something like “Copy Data”. Double-click the task to open the Data Flow tab.
Add OLE DB Source
Drag the OLE DB Source onto the Data Flow tab.
Rename it something like My Oracle Data Source.
Double-click the OLE DB Source to open the Editor.
Enter information like the following:
Use the Preview button to make sure your query works.
Create the Destination Table
Create a table in TestSSISDB using MS SQL Server Management Studio or like tool.
The table should have a ID (identity column), object_id, first_name, last_name.
Use the following to create it if you prefer:
BEGIN TRANSACTION
GO
CREATE TABLE dbo.LocalPerson
(
ID int NOT NULL IDENTITY (1, 1),
Object_ID varchar(100) NULL,
First_Name varchar(500) NULL,
Last_Name varchar(500) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
Add Copy Column Transformation
Drag the Copy Column Data Flow Transformation to the Data Flow tab.
Connect the arrows to and from this transformation to the data source and destination.
Double-Click transformation and click the columns you want to copy.
Add OLE DB Destination
Drag the OLE DB Destination onto the Data Flow tab.
Rename it something like TestSSISDB SQL Destination.
Double-click the OLE DB Source to open the Editor.
Enter information like the following:
Be sure to select the right connection manager.
Review the Mappings, and Advanced Options.
Configuring Encryption of Sensitive Data
It is important that you configure how encryption will be handled. To do so, make sure the Properties panel is visible. Click the page / background on one of the tabs (i.e. Control Flow or Data Flow). You will see a lot of properties in the panel. At the top of the panel it should say Package <package name>. Enter a password in the PackagePassword field, and change the Protection level to EncryptSensitiveWithPassword. Note that the password will not show in the connection strings or in the PackagePassword field. It should look something like this.
Build package
Testing Package
F5 to run.
Deploying Package
Locate the tnsnames.ora file and make the same change as we did to the development machine. Be sure to test the configuration as we did for the development machine also.
Copy Package.dtsx file to server.
Open up MS SQL Server Management Studio and connect to the Integration Services found on the server where it will run.
Under Stored Packages | MSDB right click MSDB and choose Import Package…
Import package as shown below:
Be sure to select “Encrypt sensitive data with password” from the Protection Level field. If you don’t, the package will run by itself, but it will NOT run as a job.
It should prompt you for the password you entered in the PackagePassword field in Visual Studio property panel for package. If it doesn’t prompt you for the password, the package in Visual Studio is not configured correctly. Be sure you built the package after configuring the password.
Test Deployment
To test your deployment, right click the package you imported and choose Run Package. If it has issues, go back to Visual Studio and add logging to a text file and consume appropriate events to help debug the package.
More information
Here is a good tutorial to get you start or give you more details than this tutorial does.
No comments:
Post a Comment