* 32 bit and 64 bit Oracle provider need to be installed on the SQL server.
* Visual Studio is 32 bit - make sure check "User 32 bit runtime" box in Execution options in Job step properties.
* It is better to use Oracle Provider for OLEDB than Microsoft Provider for Oracle. Although many people say do not use Microsoft Provider for Oracle since it's an old driver and doesn't support 64bit, I was able to use both with no problem. However, it all depends on your environment. So, it is highly recommended to go with Oracle Provider for OLEDB.
1) For Oracle connection
a) Using Microsoft OLE DB Provider for Oracle
Use server name and put the user name and password for oracle
Server name is like this. *This is the same address as TNS if you created one
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host="Host Address")(Port="Port number")))(CONNECT_DATA=(SERVICE_NAME="service name")))
b) Using Oracle Provider for OLE DB
Same as above and make sure that check save password box.
Because of Oracle log-on information need to be included with the package itself we need to pick the right package protection level in order to retrieve log-on information.
2) Package protection level
a) DontSaveSensitive
- Create a SSISConfigurationsStorage table
- note (create a view table if multiple accounts have to be used for dev, QA, UAT, etc. Since SSISConfigurationsStorage table has a ServiceAccount column, a view table will show the packagePath accordingly only to those service accounts)
- create two package configurations - environment variable and SQL server config.
- create a same environment variable on the server
- Use FilePathDestination Deployment type to deploy SSIS package. (It's nice if it can be deployed to the server directly. If it's not the case than deploy to your local machine and connect to integration service and import the SSIS package file from there)
- Create a job in SQL
- create a step and check User 32bit runtime box in execution options tab
- And it's done. The good thing about this method is that oracle credentials are stored in configuration table with connection string. It's easy to control multiple packages and service accounts in one place. Of course there are other ways of doing this but for simplicity and less maintenance purpose I think this is the best method. For instance, if the package was built with EncryptSensitiveWithPassword option, each time when someone wants to access to the package you have to type the password and need to add decrypt option in command line tab.
References
Helpful links
No comments:
Post a Comment