Post reply

ssis package creation datatype issues

  • January 31, 2007 at 8:21 am

    #116042

    Hi,

    I am trying to create a new SSIS package since when I try to migrate an existing dts from a sql server 2000 to sql server 2005, I run into issues such as "package execution failed" (or) "invalid number of arguments" if I add a package password.

    The requirement is to copy over a set of tables from an oracle db located on aix to sql server 2005 clustered environment. If the Dts/ ssis fails due to whatever issues, the dataloss should be transparent to the user.

    My approach to this is to copy over the tables from oracle using ssis as separate tables, rename current tables to tmp tables, rename copied over tables to new prod tables. If the copy over fails, drop the current tables and copy over from the temp tables back to the prod tables.

    I already installed the oracle9i client on the clustered server, created odbc connection to connect to the db. It works fine. Started business development studio and created a new package. Ran the export/import wizard to create an ODBC connection to oracle. I could not find a suitable one. Finally, I selected the oracle OLEDB driver. It asked for data source name which I thought to be an ODBC connection. Well it does not work. I changed it to oracle TNSNAMES and it works. I have set the target to SQL native client and connected to the clustered db without any issue with the target db defined. selected the create new table option and started selecting the oracle tables and was checking the destination tables. Ran into issue "The data type could not be assigned to the column "xyz" in "SQL Native Client".

    Edited the table and found that SQL 2005 is not able to assign an equivalent datatype for Oracle Number datatype (with a default size of 38). Interesting to note is that if the column in oracle is defined as number(1) in oracle, sql assigns it to be "decimal" whereas if the column in oracle is defined as number, sql is not able to match it!!!

    Did anybody come across this issue and what did you do?

    Thanks

    Murali

  • January 31, 2007 at 8:48 am

    #686876

    I compared the datatypes and plan to use numeric since both numeric and decimal ranges are from -10^38+1 to 10^38+1.

    Thanks

    Murali

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply