Run DTS from Stored Procedure and also passing variables into DTS

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
LVL 8
MartinCMSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

folderolCommented:
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.

0
Anthony PerkinsCommented:
1) You use the xp_cmdshell and the DTSRun command line.
2) You use the /A parameter, as in for example /A "MyIntVar":"3"="12345678"
0
MartinCMSAuthor 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.

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
>>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.
0
folderolCommented:
If you change your dts package to join on
select top 1 GroupName, GroupID from tmploop where ExportFlag = 'Y' order by id

you won't have to pass it parameters, you won't need a cursor, and you won't have to use xp_cmdshell, which has a little more of a permissions issue than sp_start_job.

You will need a drop table or truncate table at the bottom.  If you choose to use this I would remove the create table portion and put a truncate table at the top.
declare @nextid int
declare @lastid int
declare @selectcmd nvarchar(255)
CREATE TABLE db.dbo.tmploop(
	id int identity(1,1) not null,
	Group nvarchar(100) null,
	GroupID nvarchar(100) null,
         ExportFlag nvarchar(10) null
	)
CREATE INDEX[IX_loop_id] ON [db.dbo.tmploop]([id]) ON [PRIMARY]
 
insert into tmploop(GroupName, GroupID, ExportFlag )
Select Distinct GroupName, GroupID, 'N' as ExportFlag From myTable
Order By GroupName, GroupID 
 
select @lastid = max(id) from #tmploop
set @nextid = 1
 
while @nextid <= @lastid
BEGIN
msdb..sp_start_job @job_name='MyJobThatRunsDTS_Package'
update tmploop
set ExportFlag = 'Y'
where tmploop.id = @nextid
set @nextid = @nextid + 1
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
folderolCommented:
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
0
MartinCMSAuthor 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!
0
MartinCMSAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.