Scripting a DTS package and executing it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Scripting a DTS package and executing it

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-23 : 07:58:39

I am trying to automate the generation of excel reports. So far, I have:

A sproc that creates a new file by copying an Excel template, and appending the date to the end of the filename
A DTS to transform the result of a query (a view) to a spreadsheet

However, I need to provide the connection to Excel with the new filename, but have no idea how to do this. Is it possible to script the DTS package, and then use the script in a scheduled job to dynamically pass the connection string a new filename? If yes, How?? If not, what other ways can I achieve the same result?

Thankyou

Hearty head pats

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-23 : 08:52:18

Just copy the Excel file to another filename first, then use this filename (It shoyuld always be the same) to perform your datapump task, then rename the file to append the date..

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-23 : 09:05:16

Well, that was simple. I didn't think of that! Thank you very much!

Hearty head pats

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-23 : 09:14:12

No problem, just shows i've done this way too much..

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-23 : 11:48:28

I'm trying to use Global variables in an execute SQL task in the DTS, but keep getting an error:

'syntax error or access violation'

The query parses if I replace the ? with a parameter:

Doesn't work: SELECT dbo.fn_CREATENewFileName (?,@date)
Does work: SELECT dbo.fn_CREATENewFileName (@newFileName,@date)

What am I doing wrong?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-24 : 10:34:14

Well, I found an article to explain how to resolve my issue. Totally daft, i think, but you have to trick it into parsing the statement with a really simple statement, perform the mapping, and then put in the more complex script. And then it works fine! How rubbish!

Anyway, I now have another issue....can you use the same global variable in SQL Task more than the once? At the moment, it throws an error about invalid number of parameters (as I only have two defined, but use one twice, hence three ? but only two global variables).

Any help or advice would be greatly appreciated as I am getting fed up with it now!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-24 : 10:46:11

Ok, forget that problem, I think I was having a spaz. But this is another problem. The following code is what I have as a step in the DTS. It renames a file. The function creates the file name, and it takes 3 parameters. It works fine when run in QA but not in the DTS,claiming that I have too many parameters defined for the function?

DECLARE @newFile VARCHAR(200)
DECLARE @fileExtension VARCHAR(5)
DECLARE @renameFileName VARCHAR(200)
DECLARE @date SMALLDATETIME
SET @date = GETDATE()
SET @newFile = ? + '' + ?
SET @fileExtension = '.xls'
SET @renameFileName = dbo.fn_CREATENewFileName (?,@fileExtension,@date)

EXEC dbo.sp_RENAMEFile @renameFileName, @newFile

Sorry to keep asking questions, but I am beginning to lose the will to live trying to work on this. Especially on a friday, after having an eat all you can lunch at pizza hut, and a bottle of beer. I am very full (6 slices of pizza and salad), sleepy (the bottle of beer), and brain is refusing to work.

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-24 : 10:50:56

Right, another update! I changed the function to take only two parameters, adn now it works fine?!?!? Oh why, I don't know! And I don't care! IT WORKS...HURRAH!

Hearty head pats

bangorlad
Starting Member

1 Post

Posted - 2006-04-06 : 11:01:14

Bex,
Could you please share your wisdom with us. I am getting the same violation error, you said you had found a solution but didn't say what it was. If you could. It would be fine and dandy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-06 : 12:04:12

Hey Bangorlad

Are you are referring to the violation error caused when trying to parse a statement using global variables? If so, then what I did was to create a very simple statement such as:

SELECT * FROM <tablename> WHERE <columnname> IN (?,?)
OR
SELECT * FROM <tablename> WHERE <columnname> = ?

(Depending on how many variables you needed to use)

This would enable you to map the global variables within the statement. Then I would cut and paste the actual SQL statement to fit around the ?'s. As follows:

Step 1: Delete statement so that just the ?'s remain:

??

Step 2: paste in first part of statement (in red):

DECLARE @copyFilePath VARCHAR(200)
DECLARE @copyFile VARCHAR(200)
SET @copyFilePath = '\\fedsas21\data2\MIReports\ReportTemplates\'
SET @copyFile = @copyFilePath + '' + ??

Step 3: Paste in second part of statement (in green):

DECLARE @copyFilePath VARCHAR(200)
DECLARE @copyFile VARCHAR(200)
SET @copyFilePath = '\\fedsas21\data2\MIReports\ReportTemplates\'
SET @copyFile = @copyFilePath + '' + ?
EXEC dbo.sp_COPYFile ?

Step 4: paste in last part of statement (in brown):

DECLARE @copyFilePath VARCHAR(200)
DECLARE @copyFile VARCHAR(200)
SET @copyFilePath = '\\fedsas21\data2\MIReports\ReportTemplates\'
SET @copyFile = @copyFilePath + '' + ?
EXEC dbo.sp_COPYFile ?, @copyFile

It is important to know what ?'s you have mapped to which global variables, and also ensure that you do not cut and paste any of the ?'s, as this would delete the mapping. So basically, paste the sql AROUND the ?'s. When I ran the DTS task, it worked fine. Hope this helps you.

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources