First I am new to SSIS development, have fair knowledge of each task in SSIS. I have used the SSMA for 2008 to migrate the database objects. Can any body explain the below points what I am having with respect migrating tables data from sybase 12.5.1 to SQL server 2008. 1. For portability purpose we have plan to move the data between sybase tables to sqlserver 2008 using ssis packages. Sybase database is having around 500 tables. I am aware ssma can migrate the data If I choose SSMA what are the adv/disadvantages? What is the best practice to migrate the data between environments using SSIS with these many number of tables? 2. How to validate the data between sybase and sqlserver> 3. How to load partial loads and full loads based on the dates? 4. Do I need to develop 500 ssis packages for each table ? 5. Followed the below 3 approaches a) Truncate table -> sybase table oledb task -> sql server oledb task b) sybase oledb -> Flat file -> sqlserver oledb (Getting exception while mapping the columns of flat file connection manger configuration ) For ex: the datetime column in sybase is converted to datetime2 in sqlserver but the designer of flat file configuration columns mapping is showing different data types and the size is vary between the sybase and sqlserver) So Not able to execute the flat file ->oledb task. Could you any body suggest what is going wrong here ? c) sybase bcp out -> flat file (Get format file -- How to get format file in sybase ? ) flat file -> format file -> bcp in (sqlserver) (Do I need to use the sybase / sqlserver format file ?) Please suggest, Thanks. forums

you can use Import/Export wizard and set Sybase as source and SQL Server as destination, then check all tables to import, you can set "delete rows on destination table" in the "edit mappings" to empty destination table before data transfer. you can save this package and run it as a scheduled job . This is simplest way to implement it for 500 tables, but this will load all data every time and this might be time consuming when number of records is large. http://www.rad.pasfu.com

When you create the SSIS package with the Import/Export wizard for 500 tables, don't try to open the package afterwards. The validation of that huge package will crash BIDS :)MCTS, MCITP - Please mark posts as answered where appropriate.

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task with support of dynamic data flows at runtime. Essentially you can implement the transfer of all tables using standard For Each Loop container over the list of tables and one Data Flow Task inside the loop which does the transfer.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

But while using the import/export wizard i get an error "specified cast is not valid" when i choose the "sybase oledb provider" and enter the connection information (even the test connection succeeds) and hit next. The strange thing is I choose the same provider in SSIS and it succeeds in SSIS but the problem is I do not want to use ssis as i have to create manually data flow tasks for all the 450 tables present in my database. Please suggest on how to corect this error with import/export wizard as it will save lot of effort. Thanks, Samir samir somanchi

hi i am doing almost same thing but i am feedup now in finding Provider driver for all this... now i installed an advantage OLEDB provider but i am confused on how can i write query in sql server to use this provider just to access an test.adt file in sql server 2008