January 2, 2002 at 8:19 am
Hi All,
I know we could use DTS to export or import objects from one database to another. What I need to do is write a procedure that does this function. I am checking the books on line and came across dtsrun utility. Can this utility be used in my procedure to do the import of tables from one database to another? Has anyone done something similar? Thank you all in advance!
January 2, 2002 at 8:32 am
dtsrun is the executable that will run a DTS package. You could execute this from xp_cmdshell to run a DTS package.
Alternatively, you could schedule the DTS package and use sp_start_job to run it as well.
Steve Jones
January 2, 2002 at 8:35 am
I personally use Steve's second suggestion, sp_start_job, and it works great. Works great across linked servers as well.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 2, 2002 at 10:16 am
I am sort of confused. In that the database that I'm trying to import have many tables. I need only few of them. Also I need to import corresponding constraints and dependencies all within the procedure. Also can you please elaborate on the sp_start_job utility. Thank you so much!
January 2, 2002 at 10:27 am
Sorry, should have been more explicit. It sounds like you will need to creat a DTS package that will manage the table, index, etc moves and then schedule that package as a job (off the right click) with no repeat schedule.
Once you have the job, you can use the sp_start_job system stored procedure to execute the job that you created whenever you want from anywhere you want. If you look up sp_start_job in BOL it will give the parameters you have to provide info for.
Hope this helps and let me know if you need more information.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 2, 2002 at 11:21 am
Keep in mind that if you are importing data v objects, there are different needs. Your DTS package can be set to create the objects (and dependencies) and then move data, or jsut move data. It might help if you can better explain how this will be used over time. Is it going to be moving data every day/week/month, or a one time thing?
Steve Jones
January 2, 2002 at 11:34 am
HI Again,
In the Enterprise Manager I see Data Transformation Services. I see Local packages, Meta data services and Meta Data. Sorry for my ignarance but I saved my package in the local and executed it. It works fine but I do not see the constraints or the dependacies on the tables that I transferred from db1. How can I bring in the dependencies as well??
Thank you!
January 2, 2002 at 11:45 am
What is in your package? If you use the copy SQL objects task, then you have a checkbox for dependencies and include indexes and keys.
Steve Jones
January 2, 2002 at 12:07 pm
Hi Steve,
I do not have to schedule this job. When ever the procedure executed the target database has to be populated with specific objects from the source database.
My knowledge in SQL Server is limited as I never worked with sql server extensively. I used the DTS Import/Export Wizard and checked the (Save DTS Package box) and picked the SQL Server.
I'm not sure how to check my package code. Please can you tell me how can I access SQL Object task?
Thank you!
January 2, 2002 at 12:19 pm
Sorry! That was a pretty stupid question. I was able to execute the pkg with the corresponding constraints and dependancies. Now I guess my question is can I use this pkg that is located under DTS local Packages in a procedure?
Thank you for all your help Steve!
January 2, 2002 at 12:31 pm
That's where the job comes in. Even though you only want to execute this from a stored procedure, it is not possible to call the DTS package from T-SQL directly. I have had great success by creating a job and then using the sp_start_job to run the job which references the DTS package.
You can also use the dtsrun command line from t-sql with the xp_cmdshell extended stored proc (I would think anyway) but, it seems like it would be more difficult than using the job method. Just my opinion.
Just right click on the DTS package and select "Schedule Package" to create the job.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 2, 2002 at 2:46 pm
THANK YOU SO MUCH FOR ALL YOUR HELP!!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply