DTS and Stored Procedure (1 Viewer)

[Ice Rhino]

I have a Small peice of code I run in Query Analyzer, I then have a DTS I execute and then a further peice of Insert Into code which again I run in query anaylzer.

What is the best way to run the code, do the DTS and then run the further peice of code in such a way that someone who is totally non SQL savvy could do it by clicking on a single button or similar.

Any pointers would be gratefully rec'd.

Thank in advance

 

[SQL_Hell]

Afternoon,


schedule the DTS into a job, then add additional job steps to handle your other bits of code, or you could put all the code in your DTS (bit cleaner that way)

Then let the user run sp_start_job to execute the job, you could do this from a button on a form.

 

[Ice Rhino]

SQL Hell, thanks for the response. Do you have any idea of some sites I could go to that could assist me in creating the the form/button etc? Or could I do it in something like Access '03?

Regards

 

[SQL_Hell]

what are you currently using as a front end?

 

[Ice Rhino]

Enterprise manager. There is no front end as such. The only real front end is Crystal Reports X but that is used only by end users to run the reports against the dataset.

Regards

 

[SQL_Hell]

ok in that case you could use access to create an .adp, then use a command button to run the code via an ado connection


failing that I guess you could give user access to enterprise manager and get the suer to run the job from there.

depends how tight you want your security I guess

 

[Ice Rhino]

I think from a damage limitation point of view the Access route is a better one

If you have any advice on how to create all of this into a DTS (the clear way as you put it) or any websites I could go to then taht would be a great help

Thanks SQL Hell

Regards

Toni

 

[SQL_Hell]

Hi,

In DTS designer, you go to the task menu and click execute sql task, then paste your code in the task. Then you need to link the two tasks by highlighting them and adding a workflow (on success or on completion)

 

[Ice Rhino]

I shall give that a go, Thanks for the info

Regards

 

[Ice Rhino]

OK, I got the SQL steps all running as you suggested and boy doesn't that make life easier. I wonder if I could take it to the next level?

Is there a way that I can do the Excel part as well? This might be out of the arena of this forum but I shall ask it anyway as you seem to know your stuff. Here is the environment that I am working with

1. Every Friday I get sent a file that contains a load of data. The file name is not the same, although I can get it to be the same if that eases this process.

2. This text file is then opened in Excel and the following changes are made;
-- Column I - Number 0 decimal places
-- Column JKL - Number 2 Decimal
-- Column M - Date - dd/mm/yyyy
-- Column N - Number 2 Decimal
-- Column O - Number 0 Decimal
-- Column PQRST - Custom - dd/mm/yyyy

3. Column T1 has the value of 'Data Date' placed in it and then the date of the data being worked on added to every following row (approx 6,700 rows)

4. This is then saved as a file called latestdata.csv in a specific non changing directory on the SQL server.

5. Once saved the process of automation that you suggested takes over and completes the final steps to bring it into a SQL server table.

I guess possibly, bearing in mind that this is only data manipulation, that this conversion and saving of data could be done as part of the transformation that occurs in the automated steps. My question would then be I guess, how do I specify the format as part of the DTS and how do I get it to enter the date for the data that goes in column/row T2 onwards. Is there some way that the user could be prompted to enter the date that would be autofilled into the remaining rows?

I think you see where I am going with this. If I can do this for this package then I can apply the same procedures with modifications to other massive amounts of data imports I do on a weekly/monthly basis.

Thanks in Advance