Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chevy
Starting Member
11 Posts |
 Posted - 2008-02-26 : 09:35:49
|
| Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data. 1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype.3)When I run this package on my machine against the production server, it works perfectly.4)When I run this package on my test SQL Server against the production server,it works perfectly.5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-26 : 09:40:09
|
| What is AS/400 ? Is it different data source? |
 |
|
|
chevy
Starting Member
11 Posts |
Posted - 2008-02-26 : 10:17:35
|
quote:
Originally posted by sodeep
What is AS/400 ? Is it different data source?
AS/400 is an IBM box similar to a mainframe but it runs UDB. It can be accessed via an ODBC or OLEDB. |
|
|
|
talleyrand
Starting Member
35 Posts |
Posted - 2008-02-26 : 10:28:42
|
| Check your service pack on the production machine. I spent 5 hours on the phone with MS tech support about a year ago with a similar issue. In one of our early packages, we had designed the dataflow poorly and had 10 or so sources to destinations in the same flow (independent streams so source A to sink 1, source B to sink 2, etc). What we were seeing was that all the rows would load and under certain volumes of data, it'd populate the first X columns but x+1 columns would contain null data. It was a devilish little thing as I recall because if you put it under a data viewer, it'd always show as populated. We applied SP1 and it cleared up the issue, something to do with the stock destination component we were using. |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2008-02-26 : 10:50:07
|
quote:
Originally posted by talleyrand
Check your service pack on the production machine. I spent 5 hours on the phone with MS tech support about a year ago with a similar issue. In one of our early packages, we had designed the dataflow poorly and had 10 or so sources to destinations in the same flow (independent streams so source A to sink 1, source B to sink 2, etc). What we were seeing was that all the rows would load and under certain volumes of data, it'd populate the first X columns but x+1 columns would contain null data. It was a devilish little thing as I recall because if you put it under a data viewer, it'd always show as populated. We applied SP1 and it cleared up the issue, something to do with the stock destination component we were using.
Production Server Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Test ServerMicrosoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) My MachineMicrosoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2) As you can see my machine is the only machine where the version differs. The test and production machines both match versions but test works and production does not. |
|
|
|
talleyrand
Starting Member
35 Posts |
Posted - 2008-02-26 : 12:08:48
|
| Any chance of apply SP2 to production and see if that clears it up? I'm not sure if you have the ability or have tested it out, but I'd hate for you to pull your hair out trying to resolve something that is out of your control.Are you invoking the package in the same manner in test vs production? Maybe the oddity only happens in production because there it's fired via SQL Agent vs command line. Is it stored the same in the same location on both servers? In Test are you pulling the same data or does that query a test AS/400 instance?Are you using custom components? Any chance there could be a version mismatch on them? |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2008-02-26 : 13:18:47
|
quote:
Originally posted by talleyrand
Any chance of apply SP2 to production and see if that clears it up? I'm not sure if you have the ability or have tested it out, but I'd hate for you to pull your hair out trying to resolve something that is out of your control.Are you invoking the package in the same manner in test vs production? Maybe the oddity only happens in production because there it's fired via SQL Agent vs command line. Is it stored the same in the same location on both servers? In Test are you pulling the same data or does that query a test AS/400 instance?Are you using custom components? Any chance there could be a version mismatch on them?
Right now I cannot apply a service pack to the production server without putting on my test server first. The packages are invoked the same way on each server instance. I go the share on the production server, and double click on the package I want to run. No matter which server I run it on, they will all be pointed to the production server, but only the production server will not write the correct values to the table. Each time I do the pull the data is the same. Its a pull of every record from each file. |
|
|
|
|
|
|
|
|
SSIS Not pulling data correctly