We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Run DTS from Stored Procedure and also passing variables into DTS

MartinCMS
MartinCMS asked
on
Medium Priority
577 Views
Last Modified: 2013-11-30
Hello Experts,
I need some advice on how to accomplish this task in SQL 2000:

I have a table with different group of records. Let say&.
200 records for Group A
Within Group A there are two SubGroup ID say
100 records with ID = 111
100 records with ID = 222

What I need is to be able to dynamically export those records into two text files
File GroupA111.txt with 100 records
File GroupA222.txt with 100 records

My thoughts are:

DECLARE File_Loop CURSOR FOR
Select       Distinct GroupName, GroupID
From      myTable
Order By GroupName, GroupID

OPEN       File_Loop

      Fetch Next From CS_Loop_Fix
      INTO      @GroupName, @GroupID

WHILE @@FETCH_STATUS = 0
BEGIN

      ------------ How can I run a DTS (MyfileDTS) from here? ------------
      ----- passing both @GroupName, @GroupID variables into MyfileDTS.
      -----  I should be able to create the filename (GroupA111.txt using ActiveX task)

      Fetch Next From File_Loop
      INTO      @GroupName, @GroupID
END

CLOSE File_Loop
DEALLOCATE File_Loop

OK, so the real questions are?
1)      How can I run a DTS package inside the stored procedures
2)      How can I passing two variables into the DTS

I hope my explanation is clear enough.  I would really appreciate for any recommendation and thank you all experts in advance.

Martin
Comment
Watch Question

Commented:
To make a text file with SQL 2000 you don't need a cursor or a DTS task.  Query analyzer will export a resultset into a file.  You can do this as a windowless job running in the background in windows task scheduler, or call it from another app by using the command-line form of osql.exe.

Open a command prompt, and type osql /?
to get a slim help message.
I use osql to call a stored procedure that writes the output as text, delimited as I need it.  osql supports an output file parameter, to save the textfile to a folder.

Another way I found with google is to make the DTS package a scheduled job, disable the job, then call it from within a procedure with the system stored procedure

msdb..sp_start_job @job_name='MyJobThatRunsDTS_Package'

Within the job you can filter the correct rows into the right text file.  I'm not sure why you want to do this one row at a time with a cursor.

Tom.

CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you Folderol and Acperkins for your responses!

Folderol - the example I gave have only one group and two subgroup.  However,  I will need to process hundred of group at a time and each group have it own unique predefined DTS which I have created.  I just need to be able to pass two dynamic variable from the query into the DTS and run it from the SP inside the cursor.

Acperkins - I have thought of using the DTSRun command but not sure if it would be the best for what I need.  I will definitely give it a try and will probably need some help later.

CERTIFIED EXPERT
Top Expert 2012

Commented:
>>I have thought of using the DTSRun command but not sure if it would be the best for what I need. <<
It is not.  But given your requirements ("run a DTS package inside the stored procedures") it is your only choice.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Sorry, typo.  ExportFlag is N before it is exported so

If you change your dts package to join on
select top 1 GroupName, GroupID from tmploop where ExportFlag = 'N' order by id

Author

Commented:
folderol,

thank you for taking time to show me an alternative way.  This is great stuffs!  I can't get the /A to work correctly as acperkins had recommended.  Perhaps it was permission issue as you have said.  Anyway, I tried your method and it's works great with an exception that the ExportFlag update seem to be done prior to the job and dts finishing up.  Therefore, I would get up to three incorrect exports when looping thru 10 test jobs.  The results are consistently after testing it five times. I might need to put a check to making sure the job is finished before the update can run.

fyi - I have stated that I will have hundreds different dts.  I would have to also create hundreds different jobs to kick off the right dts.  

I really think I can try to combine both acperkins DTSRun method and your method to get the variable I need for the DTS instead of passing it in using global variable /A.

Let me try this first and I will get back with both of you soon.  Thank you both!

Author

Commented:
Thank you both and very appreciated for your insightful expertise!  I was able to use the combination of your recommendation to accomplish my task.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.