With much help I've been able to create a DTS Package and Stored Procedure that work together to automate some very tedious work.
Essentially, it looks at a predefined path for an Access database and pulls the data from it.
It uses a stored procedure from adwiseman: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20937674.html#10727302
CREATE PROCEDURE Import_files
DECLARE @script VARCHAR(8000)
DECLARE @OfficeID varchar(50)
DECLARE @path VARCHAR(8000)
DECLARE @id INT
--declaring the cursor to loop through and get file names
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, path
WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL)
--get the first record from the cursor
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
-- as long as fetch_status = 0 then a record was found by fetch next
WHILE @@FETCH_STATUS = 0
IF @path IS NOT NULL AND @path <> '' --If for some reason @path is enpty, then don't try and execute the DTS package with a bad file
--build script to execute DTSRUN dos command
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "callsImport" ' +
'/A Path="' + @path + '" ' +
'/A OfficeID="' + @OfficeID + '" '
--execute the above script at the dos shell
EXEC dbo.xp_cmdshell @script
--get the next record from cursor
FETCH NEXT FROM mdb_list
INTO @OfficeID, @path
--don't forget to close cursor, and deallocate. Very important
And that runs a DTS Package that pulls data from an MDB file defined in each office's listpath
Now, I need to add a bit more functionality....
First off, I need to delete all of the records from the Access tables 'calls' and 'clients'. I assume I am OK to just throw a couple Execute SQL tasks at the end of the current DTS Package. If my assumption is incorrect or unwise let me know.
Then, I need to do a bit more work with the data. I need to take all the data that was pulled and query it against another table. I'm not sure how I'm going to do this so I'll just post the code the way I'm thinking it.
- I have to get phone numbers from tbl_template that match the most recent listID from the current global variable value for officeID
WHERE exportID in
(SELECT top 1 listID
WHERE (officeID = @OfficeID)
ORDER BY listID desc)
- for each result record
if homephone not in (select Contact from tbl_sf3CallData) then
where homephone = homephone from results
set exportID = null
I'm almost confused trying to explain it so I'll stop there. If something doesn't make sense let me know.