Solved

Dynamic DTS and Stored Proc Help

Posted on 2004-03-31
26
862 Views
Last Modified: 2013-11-30
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
AS

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
FROM tbl_office
WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL)

OPEN mdb_list

--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
BEGIN
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
BEGIN

    --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

END
    --get the next record from cursor
   FETCH NEXT FROM mdb_list
   INTO @OfficeID, @path

END

--don't forget to close cursor, and deallocate.  Very important
CLOSE mdb_list
DEALLOCATE mdb_list
GO

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
   SELECT    homephone
   FROM         tbl_template
   WHERE exportID in
      (SELECT    top 1 listID
      FROM         tbl_sf3Lists
      WHERE     (officeID = @OfficeID)
      ORDER BY listID desc)
                  
- for each result record
if homephone not in (select Contact from tbl_sf3CallData) then
update tbl_template
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.
0
Comment
Question by:1cell
  • 16
  • 10
26 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 10727781
I'm posting to get this in my answers list, 1cell I'll be back tomorrow to help you out, that is if this is still open.
0
 
LVL 6

Author Comment

by:1cell
ID: 10734503
I've been fiddling with this a bit.  I guess what I'm going to do is add Execute SQL Packages to delete from the two tables, then add a transform data task that copies the records from the first select statement into a temp table and then runs the second query against it.  Does that make sense?  Seem like the right thing to do???

If so, I'm having problems at the first query.  I don't know how to declare a global variable value for OfficeID in the select statement for the transform data task.

SELECT    homephone
   FROM         tbl_template
   WHERE exportID in
     (SELECT    top 1 listID
     FROM         tbl_sf3Lists
     WHERE     (officeID = @OfficeID)
     ORDER BY listID desc)
0
 
LVL 14

Accepted Solution

by:
adwiseman earned 500 total points
ID: 10734624

Your going to need to add a ActiveX script task to build you SQL.  There are other methods.  Like faking out the SQL task into letting you setup the parameter.  I've explaided this before, but I think I just confused you that time.

Create an activeX task and use this this code, or something like it.


Function Main()
  Dim task


    Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_#").CustomTask    'Replace # with the number from your task.
' You can look up this number by using a dynamic properties task and browsing to your task to get it's name

        task.SourceSQLStatement = " SELECT homephone FROM tbl_template WHERE exportID in (SELECT top 1 listID FROM tbl_sf3Lists WHERE officeID = " + DTSGlobalVariables("OfficeID").Value + ")"

End Function


Now, Your going to have to write you query in this step to be complete.  Meaning, you want to take the return from above, and look the numbers up not in the tbl_sf3CallData, and updated them.  I'm not following what you want to update.  It apear your trying to update the phone number in the tbl_template table.  But this is the same as the one's we just selected.

What do you want to do with the homephone for imported data that's not in your contacts table?
0
 
LVL 6

Author Comment

by:1cell
ID: 10734929
I'll see what I can do with an ActiveX task.  
...and so you can understand a bit more about what I'm doing....

Before we get to this situation in another process, I export a list of 30,000 phone numbers from SQL tbl_template to the Access table Clients.  The 30,000 records that get exported get marked with an exportID in tbl_template.  (BTW, I'm working backwards here so I've actually not completed this part yet.  I've got test data to work with in the meantime)

Then our dialers dial the list all day.  When a number is dialed, its information is automatically entered into the Access table, Calls, by our dialers.  So any number that's not in Access table Calls didn't get dialed and needs to have the exportID nulled in the SQL tbl_template so that it can be exported again.

What you've helped me do so far is pull all of the call results from Access into SQL.  I then want to delete from both Access tables so they can be repopulated with new data for the next day of dialing.  I can do that with execute sql tasks (can I use just one task with two delete from statements???).

Now, What I'm trying to do is set the ExportID to Null for any homephone in tbl_template that wasn't dialed, so it's not in tbl_sf3CallData

I can get the most recent ListID for each Office with:

SELECT    top 1 listID
     FROM         tbl_sf3Lists
     WHERE     (officeID = @OfficeID)
     ORDER BY listID desc

Then get the phone numbers from that list with:

SELECT    homephone
   FROM         tbl_template
   WHERE exportID = [ExportID from previous query]

I was thinking I would pump that to a temp table (tbl_listRecon) and then delete what was dialed yesterday

DELETE from tbl_listRecon where homephone in (select contact from tbl_sf3CallData where startdate >= getDate()-1)

Then null the exportID for any record left

UPDATE tbl_template
set ExportID = null
where homephone in (select contact from tbl_sf3CallData where startdate >= getDate()-1)

That way the numbers that weren't dialed will be exported the next time.

And then delete from tbl_listRecon

Does that make sense???  Seem like a good way to do what I'm doing???







0
 
LVL 6

Author Comment

by:1cell
ID: 10734979
I guess I don't understand which task we're referencing with DTSTask_DTSExecuteSQLTask_#

Is it the next DataPumpTask not ExecuteSQL???
0
 
LVL 6

Author Comment

by:1cell
ID: 10735135
OK, I think I got it.  

The ActiveX task points to the next task in line and changes it's select query???

If that's correct, I've got that done and it should pumps the data to tbl_listRecon

Function Main()
  Dim task


    Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask
        task.SourceSQLStatement = " SELECT homephone FROM tbl_template WHERE exportID in (SELECT top 1 listID FROM tbl_sf3Lists WHERE officeID = " + DTSGlobalVariables("OfficeID").Value + ")"

End Function

Is that right???
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10735247
It's the Execute SQL task that you putting the bolow query it.  Actualy, the below Query should be put into the ActiveX script so it gets written into the Execute SQL  Task durring execution, but the @officeId and @ExecuteID should be replace with the value from the global variables, as seen in my script above.


This SQL will update the ExportId for all records that where not called(are not in the calldata table) for @OfficeId and @ExportID


UPDATE tbl_template
SET ExportID = NULL
FROM tbl_template
WHERE tbl_sf3CallData.listID NOT IN (Select listID FROM tbl_sf3CallData WHERE OfficeID = @OfficeID)
AND tbl_template.exportID = @exportID

Is this what your looking for., If so I don't see the need to use any temp tables.
0
 
LVL 6

Author Comment

by:1cell
ID: 10735476
I truly wish that you could see me working this all through.  I start off reading your posts, confusing them, trying to post why I'm confused, and in trying to figure out that logic actually figure out what you're telling me and it works!  I feel so ignorant.

Let me run some test data...
0
 
LVL 6

Author Comment

by:1cell
ID: 10735535
OH WAIT!

There's one problem but I think I can fix it....

it's not

UPDATE tbl_template
SET ExportID = NULL
FROM tbl_template
WHERE tbl_sf3CallData.listID NOT IN (Select listID FROM tbl_sf3CallData WHERE OfficeID = @OfficeID)
AND tbl_template.exportID = @exportID

it's

UPDATE tbl_template
SET ExportID = NULL
FROM tbl_template
WHERE [thisphone*] NOT IN (Select contact from tbl_sf3CallData)
AND tbl_template.exportID = @exportID

*thisphone = select homephone from tbl_template where exportID = @exportID

Can I do THAT without a temp_table???? lol




0
 
LVL 6

Author Comment

by:1cell
ID: 10735547
god i'm dumb

UPDATE tbl_template
SET ExportID = NULL
FROM tbl_template
WHERE homephone NOT IN (Select contact from tbl_sf3CallData)
AND tbl_template.exportID = @exportID
0
 
LVL 6

Author Comment

by:1cell
ID: 10735567
OK, when I test the ActiveX task alone, I get an error

Object doesn't support this property or method: task.SourceSQLStatement
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10735574
See, you don't even need me.
0
 
LVL 6

Author Comment

by:1cell
ID: 10735575
Function Main()
  Dim task
  Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_3").CustomTask  
  task.SourceSQLStatement = " SELECT homephone FROM tbl_template WHERE exportID = 1234"
End Function
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Author Comment

by:1cell
ID: 10735581
I doubled the points for blowin up your inbox! lol
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10735612
Change task.SourceSQLStatement to task.SQLStatement


SourceSQLStatement is what the source query of a datapump is called.  my bad.  SQL doesn't supply a easy way to get these object names.
0
 
LVL 6

Author Comment

by:1cell
ID: 10735714

Function Main()
  Dim task
  Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_3").CustomTask  
  task.SQLStatement = " SELECT homephone FROM tbl_template WHERE exportID = 1234"
End Function

:(
Invalid Task Result Value error when I ran it alone

When I ran both the ActiveX and ExecuteSQL tasks together, it took a few minutes, slowed down my server pretty significantly, finished and did not remove the exportID from records with exportID 1234.  At this point there is no data in tbl_sf3CallData so the two records in tbl_template with exportID 1234 should be effected.



0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10735757
Of course it didn't null out hte records, you didn't run the update statement.


You should be executing something more along the lines of this

Function Main()
  Dim task
  Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_3").CustomTask  
  task.SQLStatement = "UPDATE tbl_template" + CHR(13) +
                      "SET ExportID = NULL" + CHR(13) +
                      "FROM tbl_template" + CHR(13) +
                      "WHERE homephone NOT IN (Select contact from tbl_sf3CallData)" + CHR(13) +
                      "AND tbl_template.exportID = " + DTSGlobalVariables("ExportID").Value

End Function



OR for testing



Function Main()
  Dim task
  Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_3").CustomTask  
  task.SQLStatement = "UPDATE tbl_template" + CHR(13) +
                      "SET ExportID = NULL" + CHR(13) +
                      "FROM tbl_template" + CHR(13) +
                      "WHERE homephone NOT IN (Select contact from tbl_sf3CallData)" + CHR(13) +
                      "AND tbl_template.exportID = 1234"

End Function
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10735769
If you right click on the ActiveX script and execute only that step, then you can open the Execute SQL task and see the SQL script your activeX task inserted into it.
0
 
LVL 6

Author Comment

by:1cell
ID: 10735831
I had selected both tasks and right-click execute

That wont do it? It bevhaved differently than when I ran the ActiveX task alone.

On a side note, do you actually enjoy this?  be honest....  I remember when I started on this site and all I used to do was help people solve Windows and Hardware problems.  That got tedious and it was a lot more simple than this.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10735886
Things here are slow this month, right now I am enjoying it.  I actualy learn a lot by solving others problems.  I save little scripts that I write, and end up using them sometimes.

You can't execute both steps at the same time, you can do them one at a time though, the results of the first step remain in the dts package.  Try it, after executing the first step, open the second and see the changes the first step made.  then right click the second step and you can execute it.
0
 
LVL 6

Author Comment

by:1cell
ID: 10736059
If I execute the ActiveX step alone, with the original code:

Function Main()
  Dim task
  Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_3").CustomTask  
  task.SQLStatement = " SELECT homephone FROM tbl_template WHERE exportID = 1234"
End Function


I get the error...
Invalid Task Result Value
0
 
LVL 6

Author Comment

by:1cell
ID: 10736087
Oh wait, I see, it errored but it worked!
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10736107
My bad again.  It's complaining because we didn't tell the activeX script wheather it succedded or failed.


Add



Main = DTSTaskExecResult_Success





Function Main()
  Dim task
  Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_3").CustomTask  
  task.SQLStatement = "UPDATE tbl_template" + CHR(13) +
                      "SET ExportID = NULL" + CHR(13) +
                      "FROM tbl_template" + CHR(13) +
                      "WHERE homephone NOT IN (Select contact from tbl_sf3CallData)" + CHR(13) +
                      "AND tbl_template.exportID = 1234"

Main = DTSTaskExecResult_Success

End Function
0
 
LVL 6

Author Comment

by:1cell
ID: 10736296
OK, that's that.

I think you've answered another thread.

Let me ask you a theory question and I'll close this.

Right now I've got 1 dts package, callsImport and it contains;

-a dynamic properties task that assigns the officeID and path based on the stored procedure you gave me.
-then there's two connections, an Access and an SQL with a transfer datatask between them that get's the data from the Calls table
-then an execute sql task that deletes from calls
-then another that deletes from clients
-then the activex and executesql task that we(we, lol) just created.

1st question:  Is there any kind of performance or integrity issues with doing all this in one package?

2nd question:  
Export = SQL to Access
Reporting = Access to SQL
I am now going to set out on the export process that pushes 30,000 records from SQL to Access and marks the tbl_template exportID.  Do you think that should be separate from this package???  One thing that's confusing me in planning is that I can see the export mechanism will have to follow the reporting mechanism because all the data in Access is deleted after its pulled. But then what will happen if the reporting mechanism runs and there's no data???



0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10736354
I would create a separate pachage, but you will want to run them one after another.  Because what's the use in having an empty access database, you might as well full it again.  the only reason I would separate them, is to keep them in functional chunks.  You could have the first pachage, execute the second one at the very end.
0
 
LVL 6

Author Comment

by:1cell
ID: 10736394
Thanks for the continued help.  I'm sure I'll be posting again tomorrow morning once I get started on the export system.

For now I need to actually do all the things I'm trying to automate....
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now