How to create a SSIS package for data import.

Hi,

I want to create a package to import some tables from database X from Server XYZ to database X of server ABC.

(As my X database on server XYZ is gets updating everyday so i need to update it on X of server ABC using the package.)

So i have created a package using the import export data transformationn services.

It runs fine while creating. i.e importing data for the first time. But when i have saved that SSIS package on SQL or File system and scheduled it to run daily, but if fails everytime. I am not getting the error its giving. Because everytime when i go to view history of that package it just gives me messages like step1 started by user xyz and failed.

Can you please help me to sort out this problem.

If possible give me steps which will help me to create package to run above scenario.

you can mail me the solution on abhijeets@.nedbank.co.za

Thanks in advance.

Abhijeet.

The error can come from the fact you don't drop the table A before import it every time the job run your saved package.

So, if you don't append data to your table rebuild the package with Wizard and set drop table before importing (click on destination table and select "drop and recreate ...")

|||

Hi,

First of all thanks for the reply and the correct solution.

Previously i got this error when i run this job from command prompt using DTSExec utility and then to solve this i have added a step before final impot to drop the destination table but it still it get failed that time.

today after getting your solution i tried the option given by you that to tick the option of drop and recreate table.

But still it's not working, as i am running it under the Sql Server Agent account.

So i have created a credentials under the securtiy and the added proxy account in the sql server agent.

And then change the the option Run as in the step of the package to the created proxy account.

And again i have executed the job and its running now.

But if i am importing 200 tables then i have tick that option of drop and recreate tbale for every table.?

Is there any other efficient option which i can use in my package.

Thanks in advance.

Abhijeet.