| Author |
Topic |
|
sbrazier
Starting Member
19 Posts |
 Posted - 2011-11-18 : 16:38:39
|
| I have a stored procedure (requires parameters) that is a long running process. I need the ability to have the user to be able to start this process. I've just moved to SQL Server 2008 from SQL Server 2000. On the 2000 server I had created a DTS Package (which took parameters). This DTS Package executed and sent the parameters to stored procedure. To execute the DTS Package, I had a stored procedure that took parameters consisted of the DTS package and any parameters the DTS package required. This stored procedure that would create job, start the job and notify the user by email that the process had been started and they would be notified by email when it was completed.So now my question, how do I do this in SQL 2008? Do I still have the ability to do this in 2008? Can I create a SSIS package? Looking for some direction on how to handle this in 2008. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-19 : 01:56:56
|
| you can migrate the dts package to sql 2008 and do it. or another option is to rewrite functionality using SSIS. SSIS has execute sql task to call procedure to which you can pass parameters too from variables. And similar to dts, ssis also can be called from sql procedure by using xp_cmdshell and dtexec through which you can pass values for variables too.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sbrazier
Starting Member
19 Posts |
Posted - 2011-11-21 : 10:16:06
|
| Thanks!So I've created a SSIS package and have deployed it to the server. However it's not showing up in the Integrated Services IDE. I deployed it as a file system, is this the reason it's not showing. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-21 : 11:07:09
|
quote:
Originally posted by sbrazier
Thanks!So I've created a SSIS package and have deployed it to the server. However it's not showing up in the Integrated Services IDE. I deployed it as a file system, is this the reason it's not showing.
by integration services IDE do you mean BIDS or connect to Integration services using SSMS?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sbrazier
Starting Member
19 Posts |
Posted - 2011-11-21 : 22:44:23
|
| Connecting to the Integration services using SSMS |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-22 : 01:19:17
|
| did you try opening MSDB and also filesystem folders under stored packages to check?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sbrazier
Starting Member
19 Posts |
Posted - 2011-11-22 : 07:23:29
|
| Yes I check both places. It was not showing. I thought if you deployed a package (file system) it would show under the file system folder in Integration Services (SSMS). Seems you have to import the package by right clicking on the file system folder and specifying where you deployed it in order for it to how in SSMS. Is this correct or did I do something wrong in the deployment process. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-22 : 07:30:06
|
| can you tell how you deployed package?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sbrazier
Starting Member
19 Posts |
Posted - 2011-11-23 : 11:02:45
|
| I copied the deploymnet folder over to the server and then went thur the deploy utility to install it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-25 : 08:48:29
|
quote:
Originally posted by sbrazier
I copied the deploymnet folder over to the server and then went thur the deploy utility to install it.
when you installed what option you chose? File system or Server deployment?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-25 : 09:46:48
|
| Why do you want to see it in SSMS? You can just copy the .dtsx file to a folder and run it from there (usually no need to deploy).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sbrazier
Starting Member
19 Posts |
Posted - 2011-11-25 : 17:47:55
|
| I did the File System option. Great!Ok.. I have the SSIS package running. Again, I'm new to SQL 2008 and have some questions when it comes to permissions. I created a stored procedure that creates a job to execute the SSIS package. Right now I have it working from my windows application after setting my domain account as the proxy account and owner of the job. (Just for testing). So from all of the reading I have done, my understanding is, I need to create a domain account (proxy) that has the necessary permissions to SSIS and SQL Agent. Is this correct? Are there any other permission settings this proxy will need? Using my domain account is a little miss leading since I have adminstrative privilges. Will this allow the user that has no permissions, the ability to run this? |
|
|
|
|
Stored Procedure Long Running Process