Home Forums Data Warehousing Analysis Services Loading change data from AS400 (or other source systems) RE: Loading change data from AS400 (or other source systems)

  • December 17, 2012 at 4:06 pm

    #1569315

    shannonjk (12/17/2012)

    Robert, the source table is in the lookup transform itself. So I do not know of a way to feed that and change the data types. I have tried doing 'use the results of a sql query' to cast the data type but that does not work as well. Additionally doing that over 12 million rows could be a bit slow and defeat the purpose of the original point anyway 😉

    Welsh, the error is that the data types do not match, which digging into the advanced editors and looking at the input/output columns...all the data types are exactly the same. Using the Merge query yields the same extraordinarily slow results as the left join (tested and verified).

    I never stated that the MERGE would be fast and it is not because you are using an OPENQUERY. I avoid it because it is terribly slow and I could not load the data with the window that I have?

    Have you considered using the Data Conversion Task?

    I have tables that do not have Date InseDatedate Columns. I can't add columns to the tables because the AS400 is hosted by a Vendor and it is proprietary.

    I'm Using combination of Lookup Task for inserts and a Hash for Updates.

    I'm loading 100's of tables from the DB2 Data Source alone and several are greater than 12 million.

    I'm not using OPENQUERY. I'm redireting the records that fail to a text file.

    You have gotten a lot of good advise from the Forum Members, hopefully things work out for you.:-)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/