Solved

Run DTS from Stored Procedure and also passing variables into DTS

Posted on 2009-07-07
8
554 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
0
Comment
Question by:MartinCMS
  • 3
  • 3
  • 2
8 Comments
 
LVL 19

Expert Comment

by:folderol
ID: 24797853
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 24799661
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
 
LVL 8

Author Comment

by:MartinCMS
ID: 24804128
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24806120
>>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
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 19

Accepted Solution

by:
folderol earned 300 total points
ID: 24807950
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
 
LVL 19

Expert Comment

by:folderol
ID: 24807969
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
 
LVL 8

Author Comment

by:MartinCMS
ID: 24814639
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
 
LVL 8

Author Closing Comment

by:MartinCMS
ID: 31600723
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 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

13 Experts available now in Live!

Get 1:1 Help Now