0

Before you close me for a duplicate. I know there is a work around for this at the link below.

My question is: why is this "suddenly" happening? I work at company and we have probably 40+ dts packages running regularly. None have ever had this issue when going between our Oracle and SQL Server 2008 platforms.

I suspect it is because of some update to one of the platforms, or because we migrated to a new server for SQL Server.

Does anyone know what setting this would be that needs to be changed in order to avoid this?

Additionally, every package we create / edit throws warnings for this, however, every legacy package works just fine.

SSIS Package: convert between unicode and non-unicode string data types

Community
  • 1
  • 1
Holmes IV
  • 1,673
  • 2
  • 23
  • 47
  • I suspect you changed from 32-bit environment to 64-bit, try to install 32-bit Oracle drivers. – Lukasz Szozda Aug 27 '15 at 02:07
  • Read [here](https://msdn.microsoft.com/en-us/library/ms141766.aspx), check running your dts using dtexec from default and 32-bit path(C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn). You can check also if for legacy dts droping source adapter and recreating it will cause the problem. – Lukasz Szozda Aug 27 '15 at 02:12
  • If everything above not help try 1. from the data flow task, I right click on my oracle oledb source and click "Show advanced editor". 2. go to the "Input and output properties" tab 3. drill down on the "OLE DB Source Output", then to "Output columns" 4. find the column causing the error and click it 5. Then on the right hand side of the page (Common Properties) you will see the "Data Type" property 6. change this to "string [DT_STR]" – Lukasz Szozda Aug 27 '15 at 02:16
  • @lad2025 thanks reading this through I am sure this has to be the case, I am going to look more into it. Feel free to throw this down as an answer so I can close this question/pay you the bounty and most importantly stop using the conversion step! – Holmes IV Aug 27 '15 at 17:07
  • Right, I am just working to verify it. – Holmes IV Aug 28 '15 at 17:11
  • Was the data type changed in the source or destination? – Nick.Mc Aug 31 '15 at 00:12
  • The Data type did change in some cases, but implicit conversion worked. – Holmes IV Sep 01 '15 at 18:32

2 Answers2

0

You mention that you migrated to new SQL Server. I suspect that you also change architecture from 32-bit to 64-bit which can cause your problems.

To determine this is your case run your DTS/SSIS from command line using default dtexec from Program Files\...\Binn and second from Program Files(x86)\...\Binn.

Second you mentioned that your legacy dts works fine, try to delete their source adapter and recreate it and check if it'll cause your problem.

Possible solution: install 32-bit Oracle drivers and use them from SQL Server Agent Job in tab Execution Options set Use 32 bit Runtime

Read carefully 64 bit Considerations for Integration Services

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • I was never able to fully test if this would have been a solution as well, since as you can see from my answer I found something that worked without having to install any additional drivers. Thanks for helping though. – Holmes IV Sep 01 '15 at 12:13
0

To fix this issue I used the connection type: Microsoft OLE DB Provider for Oracle. Note I did not have to install any special version

This alone did not fix the issue, I also had to edit my OLE DB Souce connection properties. Under Custom Properties is the choice for AlwaysUseDefaultCodePage. This is by default set to false, when I set it to true this fixed my issue. I have not seen this error come back since.

Holmes IV
  • 1,673
  • 2
  • 23
  • 47