February 10, 2009 at 9:30 am
Can I execute a job as, say, the first step within my sproc. This job would import data from an excel file to a sql table, then the following code in my sproc would later call the sql table that was updated by the SSIS job. BTW, this job runs for no more than 10 seconds. I don't want to schedule this job b/c if the job runs when the report is run, the table may or may not exist since the job has a drop / re-create table steps. If there's a way to do this, how can I reference my job within the sproc, as in what syntax would I use.
thx,
John
February 10, 2009 at 11:44 am
Ok, you can run an SSIS package from a stored procedure. You can either enable xp_Cmdshell and execute SSIS using the command line syntax. Another option would be to create a CLR procedure that runs the package. Of these two options, using the CLR would be more secure and typically preferred.
Now, what are you trying to do? If your report is in reporting services, you can actually use an SSIS package with a data reader destination as the data source for the report (eliminating the extra table altogether).
Another, perhaps better, solution would be to use an exclusive table lock on the table when importing the data. You could then schedule the import and if the report was trying to access data in the table while your process was loading it, it would have to wait the 10 seconds (at most) for the lock to release before it could return the results. This way, you could schedule the package to load data when it becomes available rather than re-loading the same data repeatedly as the report gets run. You would also have less to worry about when two users try to run the report at the same time.
February 10, 2009 at 4:24 pm
Thanks for the detailed options. I'd actually like to explore all three, but first I'd like to explore the first one. So how would I enable xp_Cmdshell and execute SSIS using the command line syntax, assuming the SSIS package is called SME_Import.dtsx
Thx,
John
February 10, 2009 at 4:31 pm
I forgot to explain what I'm trying to do: My report is in reporting services. My datasource execs a sproc that passes all my fields except for one. This one field needs to come from a column in Excel that's manually maintained by someone. In the interest of preserving integrity as much, I figured it'd be probably best to import the daily data to a sql table, then I'd join this table in my sproc to the rest of the tables. The trick is for that table to be automatically updated, and that's why I agree with you that all three options may work but I need to explore to stick with the best one.
Hope this answers your question.
thx,
John
February 10, 2009 at 9:55 pm
Enabling xp_cmdshell is done through surfact area configuration. Executing the package is basically done calling the DTExec.exe executable.
I think you have selected the worst way to deal with your issue. I would either use an SSIS package as the report data source, or simply use the excel file as one of the data sources for the report and join the data together in reporting services rather than in the procedure. Either of these is going to be way more reliable and less problematic for multiple users.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply