?
Solved

SSIS Database Insert Issue

Posted on 2014-09-24
17
Medium Priority
?
226 Views
Last Modified: 2016-02-10
Hi,

I have a simple SSIS package that selects data from a Sybase database table and then populates the data into a different table in the same Sybase database.

I am using an OLEDB source and target.

When I run the package from my development PC it runs perfectly and inserts the data. When I run the package from my production server (SQL Server 2008) it completes successfully and inserts all the required records BUT the string fields are inserted with blank spaces to fill up the column.

For example column "Depot" is set as datatype CHAR(4) - I also tried VARCHAR(4)

> development PC inserts the depot as "BWL"
> production server inserts the depot as "BWL " with a space to make up 4 characters

I am using the same sybase database and table for development and production tests.

When I opened the package on my production server I had to change the source and target OLEDB setting "AlwaysUseDefaultCodePage" to True, whereas the package worked fine on the development PC with a value of False. If I leave this setting as False on the production server I get the following warning.
Warning.JPG
This is what the data flow looks like:

Data-Flow.JPG
Has anyone had this issue before?

Thanks, Greg
0
Comment
Question by:spen_lang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
17 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40343354
Instead of OLEDB, can you use Sybase Native Client?
About the CodePage, did you check if your PC and the Server has the same CodePage?
0
 

Author Comment

by:spen_lang
ID: 40343408
How do I check the CodePage?

The only option I have in SSIS Data flow is ADO NET Source or OLE DB Source, where can I find the Sybase Native CLient?
0
 

Author Comment

by:spen_lang
ID: 40343411
I am using OLE DB instead of ADO NET as I needed to pass parameters to the query and therefore I am using a "SQL command from variable"
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40343418
In Command Prompt run the command CHCP for the codepage.
I think Sybase Native Client should comes when you install the Sybase client.
0
 

Author Comment

by:spen_lang
ID: 40343428
Both server have an active code page of 850. The package default code page is 1252.

I have changed the provider of the data source to "Native OLE DB\SQL Anywhere OLE DB Provider 11" rather than ODBC but I still have the same issue...
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40343432
I don't think the code page have something to do with it. Should be something that differs from both machines.
The normal behavior is that spaces are used to fill a CHAR() column. The columns has exactly the same datatypes at source and destination tables?
0
 

Author Comment

by:spen_lang
ID: 40343447
The source is a stored procedure. I have changed the target table to varchar(4) but I get the same problem. This was originally set to varchar(4) then changed to char(4) to see if it made a difference...

Very strange issue.
0
 

Author Comment

by:spen_lang
ID: 40343457
It must be a problem with the 64-bit driver installed on the production server for sybase anywhere. If i change the project debug option run64bitruntime to false then the insert is correctly performed without blank spaces. Can I run the package as 32bit although the server ssis installed on is 64bit?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40343470
Really strange behavior.
I don't see why you can't run the package as 32bit. It's allowed and you already saw that works.
0
 

Author Comment

by:spen_lang
ID: 40343474
How do I run a package as 32bit instead of 64bit?
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40343485
You need to run it with the 32 bit executable:

DTExec 32-Bit  should be in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn
DTExec 64-Bit  should be in C:\Program Files\Microsoft SQL Server\90\DTS\Binn
0
 

Author Comment

by:spen_lang
ID: 40343489
OK so the problem exists somewhere with the 64bit driver and I do not know how to fix this so i am forcing the package to run as 32bit as this works.

thanks for all your help with this.

32 bit setting
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40343494
Maybe you installed a 32bit's Sybase client. Can you check that?
0
 

Author Comment

by:spen_lang
ID: 40343540
Both 32bit and 64bit drivers are installed on the server. A third party company installed these drivers for us and it is working OK with SSRS so don't want to make changes without knowing what I am doing. I have contacted the third party company to have a look.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40343546
Wise decision :)
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40343903
If you use char type then that's expected behaviour. If varchar then you may have encountered a bug. If you haven't managed to get rid of it I'd suggest that the easiest way to proceed would be to run an Execute SQL task to trim the spaces after filling the fields with data ....

hth

Mike
0
 

Author Comment

by:spen_lang
ID: 40344010
The datatype is varchar, I was thinking along the same point of creating an sql task at the end. But then I found out that I could force the package to run using the 32bit driver, which does not have the bug...
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question