Aim :- The main aim of this article is to encounter the SSIS code page warning message at the time of extracting data from Oracle to SQL Server.
Description :- Below is the code page warning message, I faced recently at the time of pulling data from “Oracle to SQL Server”.
Here, I will show you how to solve this warning (Code page warning message) from scratch. Usually, Code page warning message in SSIS is a kind of alert to the user which states that SSIS is unable to fetch the code page of the Data source. In this article, I am taking my source data from Oracle and will store this data into my local SQL Server using a SSIS package.
Solution :- We can overcome this OLE DB SSIS Code page warning message by changing the property of OLE DB Source Data Flow Component. The name of this property is AlwaysUseDefaultCodePage. By default the value is False, change it to True and rebuild your SSIS project.
Practical – Code page warning message in SSIS
Step 1 : Create and configure your new SSIS package.
- Create one SSIS package and name it “Code page”.
- Drag and drop “Data flow task” to the control flow. Double click on “Data flow task”.
- Drag and drop OLEDB source to the “Data flow “.
- Double click on OLEDB source and you will see the following window.
- It’s time to create connection manager so as to connect to the Oracle source. To create connection, click on “New ” button.
- Once you click on “New” the below window of connection manager will pop up.
- In this wizard, go to Provider drop down list and choose appropriate drivers for Oracle i.e. “Microsoft Oledb provider for Oracle”.
- Once you select the provider for Oracle, wizard will looks like as below image.
- Now, provide server name as your oracle machine name. For example – “ABCDE” and provide appropriate credentials for that server. Click on “Test Connection” button to validate your connection.
NOTE – I am not showcasing my server name and credentials as these are confidential.
- By observing above pop up window, I can say that my connection was created successfully with remote Oracle server. Now, click on OK button to proceed.
Step 2 : Configure your OLEDB component.
- Once we are done with the process of creation of connection manager, below image shows how OLEDB connection manager wizard looks like.
- Here, I have taken data access mode as “SQL Command” and entered my query (you can write your sql query).
- Once we complete this then click on “Parse Query” button to check any syntactical errors if it exists in query.
- SQL statement was successfully parsed. This show that our SQL query is correct and we are good to go ahead.
- Now, click on “OK” button to end the process of OLEDB source configuration.
- Once we clicked “OK” button, the main problem comes. The issue we faced is the warning message what I have put at the starting of this article. Once again I will give you the screen shot below so that you do not have to scroll back.
- Don’t you worry about this. Solution is explained right below. Just click on “OK” and again click “OK” button.
What actually this SSIS Code page warning message says – Let’s Analyze !!!
This is actually a warning not an error by OLE DB. This warning occurs in SSIS project while we do a Data transfer from a source (Oracle) to a destination (SQL Server) via OLE DB. This is because for the Oracle database the default code page is missing. Due to this it leads to bad data conversions. SSIS supports the option of specifying a per-column Locale Identifier for string data types, such as Character. However, the OLE DB Provider for Oracle does not support this option.
Solution :- We can overcome this OLE DB warning by changing the property of OLE DB Source Data Flow Component. The property name is “AlwaysUseDefaultCodePage”. By default the value of AlwaysUseDefaultCodePage property is False. Change it to True and rebuild your project.
Step 3 : Change “AlwaysUseDefaultCodePage” property to TRUE.
- Up to above step we done with the configuration of OLEDB source and we have seen the Code page warning message. I provided the information about that code page warning message in the above steps. Below screenshot will give you an idea how “oledb source” will look like with warning symbol.
- Now, we can go ahead and take OLEDB destination to load our source data from Oracle to SQL Server.
- Drag and drop OLEDB destination to the “Data flow” and then configure this Oledb component.
- Once we are done with the configuration of both OLEDB (source and destination), our SSIS package will look like below screenshot.
- But, still that warning symbol exists ant the question is why? This is because we have not set the property of AlwaysUseDefaultCodePage.
- If we do not set the property and click on start button to execute our ssis package, then it always throw code page warning message. Find screenshot below to see the ssis code page warning messages.
- To avoid these above warning messages, we must set “AlwaysUseDefaultCodePage” property to “True”.
- Select “OLEDB source” and press F4 button for properties or alternatively you can go at property windows for oledb source.
- Now, go to “AlwaysUseDefaultCodePage“ property and set it to “True“.
- Please take a look at below screen shot . I set that property value to “True” and then automatically the warning symbol was gone.
- We have learned how to connect to remote oracle server.
- What is the driver we can use to connect to Oracle server.
- What type of warning message we got at the time of connecting to remote Sql server.
- How to avoid SSIS code page warning message by setting “AlwaysUseDefaultCodePage” property to “True”.
This makes an happy ending to our post on How to avoid SSIS Code page warning message. You could have analyzed that a small property can make your life hard during SSIS package execution. We will post more SSIS scenarios in future. Any queries regarding this post or any question you are struggling with, do post below as comments. We will be happy to revert you with suggestions on it.