| Author |
Topic |
|
rwaldron
Posting Yak Master
131 Posts |
 Posted - 2009-12-16 : 07:12:02
|
| Hi all,I have an SSIS package that exports a table from sql 2005 to xls.I then have a send email component that attachs this file to an email and sends email to address specified in email component.This all works great !!How ever I want the end users to be able to initiate the execution of the SSIS package.Can someone point me in the right direction.I know I need to use variables.What I would love to do is to have a webpage that contains 3 textboxes,email address, startdate, endate.Is there away to pass these as variables to SSIS Package.therefore the user receives email with data from startenddateThx for any help.Ray.. |
|
|
JAG7777777
Starting Member
25 Posts |
Posted - 2009-12-17 : 04:28:24
|
| Hi Ray,I don't think you can do this exactly as you state as there are only so many ways you can execute a package (DTEXEC, DTEXECUI, SQLAgent & Manually within BIDS) - but there are perhaps a couple of options:* Use SSRS and have SSIS populate a table which SSRS uses. Set up your reports with user parameters and then allow them to export the report to XL (this is really the correct approach).* Have your users save delimitted text files to a centrally stored file. These files would contain their email address and the report parameters etc. Then, have your package loop through the centrally stored file and export for each text file found (not ideal, but a solution that does not involve SSRS).To my knowledge, you cannot email to a package direct and invoke it.You can email from SSRS with links to the report or the report itself - if you haven't already done so, you should check out SSRS........SSIS and SSRS running together make for a very flexible set-up.Regards,JAG7777777 |
 |
|
|
rwaldron
Posting Yak Master
131 Posts |
Posted - 2009-12-17 : 05:42:41
|
| Thx for pointing me in the roght direction.I'll use SSIS for reports that users want emailed automatically say once a month.For any user interaction I'll have a look at SSRSRay.. |
|
|
|
JAG7777777
Starting Member
25 Posts |
Posted - 2009-12-18 : 12:27:23
|
| No worries Ray....just so you know, SSRS Report Manager is very much designed to email reports with specific user requirements (i.e. you can have one report but with multiple paramters that are used for each email addressee or email distribution group set up in a list of subscriptions). This makes it very easy to standardise your reporting platform.Cheers,JAG7777777 |
|
|
|
rwaldron
Posting Yak Master
131 Posts |
Posted - 2009-12-18 : 12:57:58
|
| Thx JAG,I've just done my first standard report using SSRS !!Export to Excel is extremely slow..Any ideas?Export to csv is much quicker but not an option .Ray.. |
|
|
|
JAG7777777
Starting Member
25 Posts |
Posted - 2009-12-18 : 14:53:37
|
| Hi Ray,My experience of SSRS (2005) exporting to XL (from Report Manager) is that it is very quick - even complex reports...? No more that about 30 seconds tops.....and that has to be far quicker than invoking a package and waiting for it to run.How are you exporting to XL? JAG7777777 |
|
|
|
rwaldron
Posting Yak Master
131 Posts |
Posted - 2009-12-21 : 05:43:36
|
| HIya Jag,Just a normal tabular report with startdate and enddate variable.I'm using the export to Excel option that comes with SSRS DRopdownlistDDL Options are XML,CSV,TIFF,PDF,WEB ARCHIVE,ExcelIf I export a days data (130 Records) thenExport to CSV takes 1sExport to excel takes 1sIf I export 4 Months of data (about 10,000 records)Export to csv takes 10 secsExport to excel takes 1minute 45 secsThere is defo some issue regarding exporting large amounts of data to excel from SSRS.Have you seen this before ? |
|
|
|
JAG7777777
Starting Member
25 Posts |
Posted - 2009-12-22 : 03:05:49
|
| Hi Ray,10,000 records is quite an amount of data - and perhaps not a typical report export..... :-(I'm not sure what the best approach would be if you do need to change the report parameters every time......I guess you have a few options, but none are going to be really quick because of the amount of data being imported into XL:* Linked report from each user's XL application* Loop command in SSIS that analyses a list of text files (containing each user request in a directory of text files) and send out a delimitted text file to each address* Stick with a basic SSRS report and accept that 10,000 records is going to take some time to export to XLI'm not sure if 2008 SSRS has application support for rendering in XL....I know SSIS has added functionality in this area.Sorry I couldn't be of any more help Ray.Kind regards,JAG7777777 |
|
|
|
behrman
Yak Posting Veteran
76 Posts |
Posted - 2009-12-24 : 09:42:33
|
| Hi,Yes. You can pass values into your package via the command line. This tells you how to do it: http://blogs.conchango.com/jamiethomson/archive/2004/12/13/451.aspx[Apologies on behalf of my internal IT department if there is a fault with this link. They've been promising to move us to a new blogging engine where this won't happen for about 6 months now and still nada. Hopefully the link will work]RAQ Report: Web-based Excel-like Java reporting tool |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-12-30 : 11:56:13
|
yes you can have users start SSIS packages on demand and also pass in variables. What you do is call a stored procedure that has the followingCREATE PROCEDURE dbo.usp_StartAgentJob @SQLJobName VARCHAR(100)AS BEGIN EXEC msdb.dbo.sp_start_job @SQLJobName; END Give users Execute rights to this stored procedure. Call this via adodb call.And like JAG7777777 said have the SSIS package call a report on the report server to generate an Excel file to a certain location and attach that in your email. It has worked like that for us.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
Send Variables to SSIS Package