March 6, 2013 at 7:21 am
Hi all,
I just want to discuss a method with you to discover the column data types of a table in a DB2 database. I not own the documentation and only a reduce set of tables are exposed, I mean, I only have read access to 4 tables.
At the moment I select all of the columns using a OLEDB Source (MS DB2 Driver) and then connect this source directly to an OLEDB Destination, SQL Server database, and press the "New" button as the target table, just to let SSIS determine (or guess) the columns data type.
The SSIS is good at guessing but I would like to make my own method, like a basic verification of available types.
For instance, if a column has only numbers is numeric, but if it has numbers, NULLs and empty values ("") could also be numeric. But the empty values make the SSIS to marks it as a VARCHAR column, even if the column does not have alphabetic characters.
Any ideas or comment would be appreciated.
Kind Regards.
March 7, 2013 at 10:52 am
how are the tables exposed. Are they on a linked server.
Can you ask the vendor for table definitions. You will probably find that you are not even looking at the tables but some views generated specifically for exposing data to you.
you could try treating each column as varchar until you know better and use a dataConversion task to interrogate rows and change them to the desired values but this would not provide you with a generic approach.
Good luck if the DB2 vendor won't help you
Obiron
March 8, 2013 at 1:17 am
Hi Obiron,
Thanks for your comment!
It is a linked server in my Management Studio.
The customer did exactly what you said. All of the fields are at the end Strings. I discovered it because there are no NULL values in the tables, instead there are empty or blank spaces (""). Then depending on the context I treat a field as a numeric, string or datetime field.
The problem here is the customer hired a company for the cash desk system of its stores, another company for the online shop and then our company to create a CRM system, so I have to wait a lot of time until somebody decide who is responsible to deliver documentation and so on.
In previous experiences I've faced similar situations and at the end I received the documentation, but after one or two weeks.
I think it would be nice to develop a tool or a plugin to aim the auto discover data types and length of a given table. I know that SSIS has something similar to tables and flat/csv files, also Pentaho has something similar to csv files, but I don't know exactly how they work internally.
Kind Regards.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply