For reasons best kept to myself, I have been trying to move some data from SQL Server 2008 into Oracle on two unconnected machines. The best way of doing this – given the tools to hand* – appeared to be export from SQL2008 to flat files and suck it into Oracle via Data Manager (DM). Because apparently a simple “import text file” feature in Oracle is too much to ask for from most client tools. All hail SSMS, all curse SQL Developer. Due to issues with the data containing common delimiter characters, I decided to output to a Fixed Width flat file.
I subsequently had a barrel of laughs trying to get things into Data Manager because of its expectations of what constitutes a flat file versus what SSIS thinks is a flat file. For once i’m going to blame both parties equally for this mess, as both tools do odd and annoying things in a very non-transparent manner.
DM didn’t take the default flat file as it didn’t acknowledge what passed in SSIS for a row delimiter (the character at the end of the line that signifies a new row) so I decided to switch to Ragged Right as that has a specifiable row delimiter. Data Manager liked this a bit more, but complained that the last column of each data file terminated unexpectedly early.
It turns out that Ragged Right format trims trailing spaces from the last column – so if you have a column 8 characters wide, but only 4 characters in it, the last column will show the row delimiter after the 4th character. So Ragged Right isn’t truly “Fixed Width” – more – “Fixed Width, apart from the last column”. It’s counterintuitive, but if you read the documentation, it is behaving as intended, and fortunately there is an alternative approach.
The next step was to choose Fixed Width with an added column for row delimiter (an option when building your Flat File connector). This ensures every column is output at the full width of the column, even for the last one.
Well, almost. What I discovered next is that Numeric columns have been – regardless of size – all output with a width of 260. So your single digit integer gets output as 259 characters of spaces and a single numeric. This odd behaviour is controlled by the InputColumnWidth property (found on the advanced page for each column), rather than the expected OutputColumnWidth property, which for some reason is not configurable for numeric fields. To output a column just as wide as the numeric field, you need to set the InputColumnWidth to the Precision of the field, plus one for the decimal point and plus one for a minus symbol if the field is signed. Hence a field of precision 4, scale 2 – such as 21.13 – needs a InputColumnWidth of 5 to take all the output characters. If the numeric field is signed and carrying negative numbers – you need another point on there, so -21.13 would need an InputColumnWidth of 6 to export successfully.
Of course, Data Manager expects a precision 4 numeric to only be 4 characters wide, so when setting the precision in the SQLText Designer, you need to set the precision to match the InputColumnWidth used to export the field as well.
And that, my fellow BI monkeys, is how I wasted a full working day trying to get 14 text files from one database to another. It has done nothing to endear me to Oracle, that’s for sure.
* which includes that trusty ETL tool, a USB memory stick