Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
william_lee78
Yak Posting Veteran
53 Posts |
 Posted - 2004-09-08 : 04:14:11
|
Hi All,I would like to transfer my data that is older than 20 weeks from my SQL 2000 to MS Access 2000 mdb file every Sunday. Is it possible to do it using DTS? Can I use VB to pass it some value to the DTS and trigger the data exporting process? Can someone pls guide me on how to do it? Basically, I'm new to DTS.Your guidance is much appreciate. Thank You! |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-09-08 : 04:24:00
|
| Yes, you can use DTS for this purpose. Execute the command prompt utility "DTSRUN" from VB to trigger the DTS package. Refer BOL for more info on DTSRUN.Hemanth GorijalaBI Architect / DBA |
 |
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-09-08 : 04:29:35
|
| Hi Hemanth Gorijala, Thank you for your reply. Can you pls provide me more information about this? Basically, where can I find those information? Or Do you have any samples that can show me?Thank you |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-09-08 : 07:49:04
|
| SQL Server BOL ("Books Online") should give enought information for a simple package.To start with...I'm assuming your DTS package would need to export data from one sql server table to a Access table.Open a new DTS package. Drag the SQL server Connection object on to the drwing sheet. Enter the connection properties. Similar with the Access connection object.Now highlight both the connection objects(use "control" key and click on both). Then right click on Access object and select "Transform Data Task". This will create an arrow towards Access. Double click the arrow and it will open "Transform data task properties"In the "source" tab give your source query..like.. select * from table where data > 20 weeks etc...In the "destination" tab, select the Access table.In the "Transformation" tab, check if the source column are properly mapped to destination columns. You can experiment with "Lookups" and "Options" in leisure..and you DTS package is ready...hope it helps..Hemanth GorijalaBI Architect / DBA |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-09-10 : 12:22:06
|
quote:
Originally posted by william_lee78
Hi All,I would like to transfer my data that is older than 20 weeks from my SQL 2000 to MS Access 2000 mdb file every Sunday. Is it possible to do it using DTS? Can I use VB to pass it some value to the DTS and trigger the data exporting process? Can someone pls guide me on how to do it? Basically, I'm new to DTS.Your guidance is much appreciate. Thank You!
VB is not required.DTS only can handle this. From EM start DTS, instead of selecting the objects opt for QUERY option. Write the query to transfer records older than required period.In the last step, instead of executing SCHEDULE the package for every week end. The package will be excuted every weekwend.------------------------I think, therefore I am |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-09-27 : 22:48:53
|
| Sorry for late reply. I have tried out the steps provided by Hemanth Gorijala. Its work.Thank you so much for the help. |
|
|
|
|
|
|
|
|
Exporting Data To Access 2000