May 18, 2002 at 1:04 pm
We have a situation where we need to update several SQL Server tables nightly from our Intranet site to our Internet site..
(1) We DO NOT have admin access to the SQL Server (As it's owned by an ISP)
(2) We can convert and/or upload the Update data to our ISP (Either in Foxpro table format or Text format)..
(3) Is there an easy way (Again without having Admin access to the SQL server) to import from a text (Or DBF file) to SQL?? Is DTS the answer??
(4) Note : we have tried several other approches - including Remote views etc (Too slow).. and think DTS is the answer.. As we are new to DTS, please speak slowly when replying to this post.. 🙂 (IE - details in NeeBee Language..) 🙂
Thanks Millions for ANY info!!
Rich Kopp
May 19, 2002 at 9:42 am
Rich,
There are a few ways to do this, some easier than others. Partly depends on what access you have. If you can create and schedule or run DTS packages, then I'd use DTS. It's quick and easy and straightforward. Send the files in the same format (text or Excel), with the same name and run the DTS package to load the data. This doesn't handle updates unless you want to use the Data Driven Query task. That can get complicated. Easier to load to a staging table, then update the main table from there, matching on primary keys.
If you can't do DTS, you can do Bulk Copy to load a text file using TSQL. Same process, use a staging table and update/insert from there.
I assume you can handle the SQL side. If not, let me know. I'd use DTS if you can. Setup a package with a connection for the source (text or Excel), a conneciton for SQL and use a Data Transform task to load the data. You can create a staging table from here. Then create a stored procedure to handle the update/insert and add an Execute SQL task to your DTS package. Call the stored procedure from there. Click the SQL connections, hold the shift key and right click the Execute SQL task. From there you can set an "On Success" precedence to execute the SQL task after the load.
HTH, Steve
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 20, 2002 at 5:21 am
A question for Steve...
I am familiar with the strategy of populating staging areas then updating via a task to execute SQL or a SP. However, I am curious as to why it is so complicated to do the update via a Data Driven Query.
Do you know of anywhere where there is a simple series of steps described on how to configure a Data Driven Query to perform the following typical task: ie to update a set of target rows that correspond to a set of transactions.
Regards,
Mike.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply