Simple question, In a SSIS 2008 Package is it possible to have a OLE DB Destination pointing to another Server Machine running on SQL Server 2000 to do bulk insert operation ? . what are the potential pitfalls when while inserting into SQL Server 2000 ?Anand

A: Yes B: Bulk insert = Yes C: nothing but my suggestion use a SP (stored procedure ) you dont need SSIS Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi Thanks you for your quick response , can you throw some light on how I could have a stored procedure do this .......Anand

well you need to have the connection between your SQL2008 and SQL2000 , one of them is SQL linked server, so you have to have a LIKNED server in your SQL2008 pointing to SQL2000 then in a SP use INSERT INTO LinkServerName.DBName.Scem.tblABC <---- SQL2000 and must have 4 naming you can not use Linkedservername.dbname..tblName SELECT * FROM tblABC <---- SQL 2008Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Anand: You can easily accomplish your task by using a Data Export/Import Wizard, besides, you can save its work as a SSIS package that you can later reuse in a scheduled job/batch file. But how about instead of giving you fish I teach you how to fish? Please take a look at 6 Ways To Import Data Into SQL Server: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12 PS: The Bulk Insert from a stored procedure is covered there and I agree with Nik it is the most optimal for large sizes of data, but I do not know what is in your case.Arthur My Blog

Thanks ArthurZ you are right Anand remeber that you may have to consider UPDATE as well (dont do any JOINS through the LINKED Server fro larg data)Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).