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.

how to create a DTS package in 2005

Can anyone give me a quick tutorial on how to create a DTS package in 2005 using Business intelligense studio.

I want to import a file from a ftp location and load it into the database as a table.

I know how to do it in 2000, but can use some help in 2005.

thanks

Look at the tutorials in BOL.

-Jamie

how to copy some stored procedure in SQL 2005

How to copy only some stored procedure from dev server into prod server in sql server 2005? Should I use import/export wizard or copy database wizard? Thanks in advance.

Right click on the database in the object explorer, goTasks->Generate Scripts. SetScript Object-Level Permission toTrue, click Next, check only theStored procedures, you will see a list of SPs after clicking Next. Pick the ones you like and go Next, then select preferred script mode.

|||

Thanks jack for your reply. Is there a way to differentiate between stor proc that is already in the prod server and which are not? Thanks.

|||

It's better to use 3rd party tool to handle the comparisons. Even if a SP exist in the production server, it does not mean it's has not been altered on your dev server.

Here's a good but expensive one:http://www.red-gate.com/products/SQL_Compare/index.htm

Visual Studio Team Edition for Database Professionals edition can do this too, but it's even more expensive:http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx

|||

Thanks.