How to avoid SSIS Code page Warning Message?

3
1187

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”.

Code page warning message in SSIS
Code page warning message in SSIS

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 AlwaysUseDefaultCodePageBy 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”.

Data Flow Task in SSIS

  •  Drag and drop OLEDB source to the “Data flow “.
  • Double click on OLEDB source and you will see the following window.

OLEDB Source Editor in SSIS

  • 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.

Connection Manager in SSIS

  • 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.

Configure Connection Manager for Orcale in SSIS

  • 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.

Test connection pop up in SSIS

  • 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.

OLEDB Connection manager wizard

  • 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 successfully parsed

  • 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.
SSIS Code page warning message
SSIS Code page warning message
  • 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 “AlwaysUseDefaultCodePageproperty 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.
Warning message showing on OLEDB source component
Warning message showing on OLEDB source component
  • 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.

OLEDB source to OLEDB destination

  • 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.

Warning messages while executing SSIS package

  • To avoid these above warning messages, we must setAlwaysUseDefaultCodePage” 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.
Set AlwaysUseDefaultCode property to TRUE
Set “AlwaysUseDefaultCodePage” property to TRUE

Summary :-

  1. We have learned how to connect to remote oracle server.
  2. What is the driver we can use to connect to Oracle server.
  3. What type of warning message we got at the time of connecting to remote Sql server.
  4. 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.0/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 5 votes)
How to avoid SSIS Code page Warning Message?, 9.0 out of 10 based on 6 ratings

3 COMMENTS

  1. If I need to run the package on different code pages, different collations then how will I set the code page in my ETL package?

    Default code page in ETL is 1252 but on production system, it can be different. How can we handle this issue in ETL packages

Comments are closed.