How do object become impacted/invalid while migration and how can we re validate them?
November 1, 2016
PMCMD Commands In Informatica
November 8, 2016
Show all

Which ODBC driver need to be selected in order to use SQL Server 2014 source System?

You can connect to Microsoft SQL Server using the DataDirect 7.0 New SQL Server Wire Protocol Driver. The DataDirect Wire Protocol driver for Microsoft SQL Server removes the need to connect to the database through connectivity components on the database server. The driver can connect to any Microsoft SQL Server instance. The DataDirect Wire Protocol driver for Microsoft SQL Server also removes the dependencies on database client libraries.

Configuring an ODBC Connection on Windows:
1. Log in to the system where the PowerCenter Integration Service runs.
2. In the ODBC data source administrator, select the System DSN tab.
3. On the System DSN tab, Click Add.  The Create New Data Source window appears.
4. Select the DataDirect 7.0 New SQL Server Wire Protocol driver.
5. Click Finish.
6. Enter the data source name, host name, the port number, and the database.
7. On the Security tab, select 4 – Kerberos as the authentication method.
8. Test the connection with the Test Connection button.
9. Click Apply.
10. Click OK.
Configuring an ODBC Connection on UNIX:
1. Log in to the system where the PowerCenter Integration Service runs.
2. Create an entry in the odbc.ini file to add a data source name for the Microsoft SQL Server ODBC driver.
3. Ensure that you set the Authentication Method as 9, so that the driver uses the NTLM authentication.
Sample Entries in the odbc.ini File
Driver=< driver location>
Description=SQL Server DSN
Database=<database name>
Domain=<Windows domain name>
Create an ODBC Connection in the Workflow Manager
On Windows, ensure that the PowerCenter Integration Service uses the same Windows NT login as the Microsoft SQL Server connection. On LINUX, ensure that the PowerCenter Integration Service uses the user details in the connection object for authentication.
1. Log in to the Workflow Manager.
2. Go to Connections > Relational.
3. Click the New button.
4. Select ODBC as the subtype.
5. Click OK. The Connection Object Definition dialog box appears.
6. The properties that you configure for an ODBC relational database connection can be seen here.
7. Click OK to save the connection to the repository.
8. In the Workflow Designer, open the workflow.
9. Double-click the session where you want to use the ODBC connection to open the session properties.
10. On the Mapping tab, select Sources in the Transformations pane on the left.
11. In the Connections settings on the right, click the Open button in the Value column for the relational connection. The Relational Connection Browser dialog box appears.
12. Select the type as ODBC.
13. Choose the connection that you previously created.
14. Click OK.
15. Repeat process for all source and targets in the Transformations pane.
16. Click Apply.
17. Click OK to save and close the session properties.
18. Click Repository > Save to save the session and workflow to the repository.
Now, this connection can be used to import the sources/targets and also to use in the session properties for the data flow to happen.
Business Intelligence Consultant and Trainer with 13+ years of extensive work experience on various client engagements. Delivered many large data warehousing projects and trained numerous professionals on business intelligence technologies. Extensively worked on all facets of data warehousing including requirement gathering, gap analysis, database design, data integration, data modeling, enterprise reporting, data analytics, data quality, data visualization, OLAP. Has worked on broad range of business verticals and hold exceptional expertise on various ETL tools like Informatica Powercenter, SSIS, ODI and IDQ, Data Virtualization, DVO, MDM.

Leave a Reply

Your email address will not be published. Required fields are marked *