SSIS issues after changing servers with character sets or code page or whatever its called
-
I am having my ssis package fail when converting characters not supported by our iseries. This package used to run fine on Windows 2008 svc pack 2 but now failing once switched to run on windows server 2012 R2 and I can't figure out how to resolve.
Someone suggested it is with different code pages or collation or regional settings etc which I know nothing about. I am just trying to determine where to look on both servers to find the difference and make the 2012 the same as the 2008 R2 was. I'm a developer not a system guy as you can probably tell so not sure of terminology etc. I do have access to both systems so I can look at whatever I need to if I knew what that was.
I'm hoping someone can tell me what I'm looking for and where it is on both systems. Thanks!
- Dave C. - Friday, August 18, 2017 1:52 PM
Regional settings - go to control panel and then Regional and Language settings. Refer to this -
How to: Change Operating System Settings to Support Localized Versions
To find the code page being used - open the command prompt as administrator, type in chcp and it will report the code page.
Refer to this to see what the values are:
Code Page IdentifiersTo get database collations, you can use this:
select name, collation_name
from sys.databasesI wouldn't change anything if you find any differences though.
What is not working? You said "converting characters not supported by our iseries". Converting from where to where and which is failing? Are you importing to iseries, exporting to iseries, from where to where? It may not have anything to do with what was suggested and you could definitely hose out more than your SSIS packages.Sue
-
Thank you so much for your reply. I did the chcp and both systems show IBM437 OEM United States.
Here's what I am doing. I have an ssis package that reads a sql table loaded by a SalesForce job. There is a comment column that has data that in the past when loading an iseries file caused it to fail with a message that it could not be converted to the host data type.
To resolve these invalid characters when my agent jobs ran on a 2008 R2 server I just did a replace of those values in the ssis package and everything was great. Since then we have upgraded to a 2012 server with sql 2016. The same agent job now fails with IBM error CWBNL0107. I've attached an image of the message. I have tried all sorts of things in the package from not validating metadata to forcing a code page and not forcing and whatever else I could think of and always fails.
My workaround which I finally found was to put an agent job on the DB server which is still 2008 and fix the data there before the 2012 agent job does the transfer from SFDC to the iseries. Something is different since going to the new server but I have no idea what or how to pinpoint.
Appreciate the assistance. Please let me know if you need more information.
- Thanks. It's been awhile since I did some SQL Server and AS400 imports/exports but you are in the right area since you are getting errors for unsupported characters. Most of the times I hit these, they were due to driver issues - version or missing some configuration setting. You would want to make sure you have the latest driver. For the settings, the only ones I remember messing with for the error is the Force Translation and CCSID but don't remember any of the other specifics. It used to be that if CCSID wasn't specified, it would use the client default. Not sure if that's still the case with the current drivers.
But I would first guess that the issue lies in this area somewhere. I would leave the settings on the operating systems alone.
Have you tried hitting the IBM forums? I used to get most of the information for those issues on IBMs site. It's not that unusual of an issue in that world...if it's any consolation.Sue
-
I kind of figured it's in this area but would like to see a specific place on both servers to see a difference so I know once changed I have it resolved. Old server has a different version of client access v5r4 and new is v7r1. Looked at IBM I Access for Windows Properties and appear the same, specifically the Language tab. One thing I am noticing on that tab is all are set to Default. Where does default come from which could be the issue on the different systems. Wondering if the three parms are default for the language selected. I guess I will have to check on IBM sites as you suggest to find the answer to that. Most seem pretty stale.
Thanks for the assistance. Would like to nail this one down. They are going to remove the old server soon then I will have nothing to compare to what worked.
-
The whole defaults thing is one of those that burned me with those drivers. What I learned on IBMs site somewhere was the the defaults for CCSID will be based on what the client is using if you haven't specified a value. And there have been changes made to what collations, code pages are available on different versions at least on SQL Server in between the 2008 and your versions now. Not sure if its the same with the OS or not. I thought it was but not totally sure. But in any case, you'd want to do any changes needed with the driver rather than the systems themselves.
The driver versions I know we had those set the same but the differences can still bite you. At one point I did have to change settings so it would work after changing the drivers - so the settings between old and new were different to get things working. I think as long as you know the versions of everything on the old box, you will probably be okay in trying to track it down. Try searching on SQL Server AS400 and client access. I just tried that and could get a lot of hits on the driver issues between iSeries and SQL Server.
If you find anything please post back. I'd be very interested in know what the fix is and I do really think it's doable. Just need to find the right settings.Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply