Oracle and SQL Server 2005

I have more than 8 GB Data in oracle and Everyday we have to check some data in oracle but it takes times due to lot of data. So what i was looking that data that we need can import in SQL Server database and do checking purpose. But I was looking some command that connect to oracle brings its data and import in SQL Server. For that we will fire SQL statement to oracle through asp.net and insert into SQL Server I will fire differencial data insert covery only. is there any way or idea to do that please advice me.

The simplest way to do it, if you just want to do it once a day, is to create an SSIS (Sql Server Integration Services) package to transfer the data.

It's a pretty easy tool to use.

Once you have that, you can set up that package to run as a batch job at a set time each day.

Take a look at SSIS, it's easy to learn and will do a fine job.

|||

Can you provide me documentation on using SSIS with refrence to my problem. Thanx in advance

|||

Hi milindsaraswala,

Yeah, use SSIS package to build a job then schedule it to run every a certain time interval is a good idea. But first make sure you have Sql Server Intergration Service installed on your machine.

As far as I know, there are at least 2 ways you can create an SSIS package. The first ways is to use Bussines Intelligence Studio (since you are using sql2005 now, you must use Intelligence Studio if you want to create a package programmatically).However, this is not what I recommend here since it's not as directive as the second one: Use Sql server Import/Export wizard to create SSIS package automatically.You can follow the below steps to build your own package and use it to build a job:

Open Management Studio and select the database into which you want to export Oracle data. Right click that db and choose "Tasks"-->"Import" , in the source data selection page, choose your oracle datasource and click next.Follow the instructions in that wizard and do remember to click "generate SSIS package" option at the last step--> this would generate a package for your and deploy it to your Intergration Service.

After you have finished that, you can open your sql server agent and choose job--> new job. In the steps option, specify the step type to SSIS package and then choose the package which your created just now(you can also view that package by connection to your Intergration service thru managment studio).

Hope my suggestion helps