Problem updating sql statements in DTS - How do I run sql query files from isqlw or using the xp_cmdshell?

Question

Problem updating sql statements in DTS - How do I run sql query files from isqlw or using the xp_cmdshell?

s padmanabhan

SSC Rookie

Points: 32
More actions
October 7, 2006 at 2:19 pm

#67645

Hello,

I have a DTS package that executes several sql tasks one after another. The SQL commands are pasted from the query into the sql statement part of the task. Problem is everytime the query changes I have to go back into the DTS package and repaste the query again. Since the package contains about 30 sql tasks, it is very time consuming.
I'd like to know if there is a way to create an sql task that will just have a placeholder pointing to the original sql file, so that whenever the sql file is update, the sql task just runs that file. This way I don't have to modify the DTS pkg and all sql tasks inside it.
Example:
Lets say I create a DTS Pkg and create SQLTask-1 and in the sql statement I say exec master.dbo.xp_cmdshell 'isql c:\file1.sql'.

Or Is there a better way to build DTS pkg calling external sql query files?. Thanks.

Cheers,Padhu

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike Chappell Old Hand Points: 390 More actions · Answer 1

By no means am I suggesting this is the ideal solution but if you are running SQL2K you can use a dynamic properties task.

Lots of our packages have details that change. Sometimes SQL, sometimes usernames etc. We use an ini file that contains those kinds of details (or sometimes another table on the server).

In your case it might work like this:

Create your datasource (whatever you are running the SQL on)
Create your first SQL task... put the command as something like "--Container 1"
Create a Dynamic Properties task (grey icon, before the pick axe)
Give it a description, like "Update SQL Statements"
Click Add
On the left, expand "Tasks" and find your SQL Task, should be called something like "DTSTask_DTSExecuteSQLTask_1" but you can check on the right when you select it because the SQLStatement box will contain "--Container 1"
Double click on the SQLStatement Box
Change the "Source" drop down to Data File
Click on the "..." browse button next the the File Box.
Find your SQL statement file (you may need to adjust the "Files of type" box
Your SQL should appear in the window, press OK, then OK Again and you should be done

Notes: Make sure the Dynamic Properties is at the front of your logic (i.e. it runs before the SQL tasks do) and that if you are referencing a file then all the accounts that might need to run the DTS have access to that file.

Personally I'd recommend referencing the file with a UNC style... like \\servername\sharename$\filename.

Mike

James Carmichael SSC Rookie Points: 39 More actions · Answer 2

What properties are changing in the SQL Statement? If it's filtering, like a date range on a package that's run on a time schedule, like a weekly or month-end process, you can use an execute sql or activex script task to resolve the dates and save them to global variables, then use the variables as parameters for the other tasks.

s padmanabhan SSC Rookie Points: 32 More actions · Answer 3

Hello Mike,

Thanks for the detailed instructions. I followed all the steps and got stuck in the last step.

I am currently having all the sql tasks kicking one after another in a sequence (task1 --workflow on completion -task2 and so on) inside the DTS package. So when I am done creating the above steps, I see two icons i.e one for dynamic properties task and another one for the container task. So which one should I link for the workflow to maintain the same workflow. Appreciate your help again.

Hello James,

These are actual SQL query files run inside the DTS package. The code itself has various filters and those changes, sometimes the sort order changes, sometimes additional columns are added. I use editpad lite, So I open all 30 files once and do a find replace and save all. The problem is I need to manually copy and paste all the updated code inside the DTS sql tasks again. Hence this question. Above provedure from Mike looks very promising.

Thanks again for your reply.

Cheers,Padhu