January 17, 2011 at 7:57 am
Hi,
I am new to SSIS, I need assistance in creating a package that will export data to an excel and then send an e-mail to the users. We are planning to use the SMTP mail functionality for sending e-mails to the users. Since i am new to SSIS, i need some guidance on how to create a package.
If possible please provide me some tutorial links for SSIS.
Thanks,
Srikant Epari
January 17, 2011 at 8:31 am
Hi,
I too am just starting out with SSIS. The tutorials i've been working through are these:
http://msdn.microsoft.com/en-us/library/ms167031.aspx
You may find that of some use. Are you sure that SSIS is the tool you want to acheive what you're after? SQL Server Reporting Services might be better for you. You can write reports that export to .xls and setup subscriptions etc. that deliver to multiple recipients. It's fairly straightforward to setup.
January 17, 2011 at 9:06 pm
Hi,
Thanks for the link. We do not have SSRS setup on the servers and the client wants this report to be done using SSIS.
Do you have any other links which can help me in creating this kind of package.
Thanks,
Srikant
January 18, 2011 at 1:55 am
It may sound crazy (like it did to me at first) but YouTube is a fantastic resource for online video tutorials on all sorts of SQL Server related technology. Have a look on there. 🙂
January 18, 2011 at 2:58 am
Another great resource for SSIS tutorials:
(sorry if this seems cynic, but there are tons of good tutorials and articles out there. A little effort won't harm you...)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 19, 2011 at 2:15 am
If you want to do more than just export some unformated data to Excel
you will have a steep learning curve using SSIS. You have to learn VB script.
You have what I use two alternatives:
VBA in Excel. Works excelent if You know Excel and VBA
VB.net There is a lot of stuff on Internet. Just use Google.
One start is http://support.microsoft.com/kb/302094.
or http://support.microsoft.com/kb/257819.
But be aware! When you create an instance like:
' Create a new instance of Excel and start a new workbook.
objApp = New Excel.Application()
That instance will not be killed with:
objApp.quit
You need to include something like:
processes = Process.GetProcessesByName("Excel")
For Each proc In processes
If processes.Length <> 0 Then
proc.Kill()
End If
Next
Best luck
Gosta Munktell
January 19, 2011 at 6:52 am
What you want to do can be as easy (or as hard) as you want it to be. Look up SSIS tutorials on Google or Microsoft's websites. Books Online is also a wonderful resource.
January 19, 2011 at 1:35 pm
Taking a minimalist approach, SQL Server's Import and Export Wizard, which is accessible by right clicking on a database in SSMS, provides the option to save the process to a re-usable SSIS package. Depending on how complicated or configurable you need the extract process to be, you can then use the package as is or as the starting point for a project in BIDS.
http://technet.microsoft.com/en-us/library/ms186943(SQL.100).aspx
If you know how to code T-SQL, then the simplest way to handle data transformations, like concatenating or stripping columns, is just to do it in your SQL select statement. In most cases you won't need to resort to any of the more advanced Data Flow tasks or a retro VBA script technique.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 20, 2011 at 5:26 am
Eric Russell 13013 (1/19/2011)
Taking a minimalist approach, SQL Server's Import and Export Wizard, which is accessible by right clicking on a database in SSMS, provides the option to save the process to a re-usable SSIS package.
Which doesn't take care of the email part of the package.
Eric Russell 13013 (1/19/2011)
Depending on how complicated or configurable you need the extract process to be, you can then use the package as is or as the starting point for a project in BIDS.
True. But he'll still need to learn SSIS somehow. And it's better he does it from scratch if he wants to understand enough to support it properly.
Short cuts are always good, if you understand how they work and how to fix them when they break. But if you don't know how to swim, jumping into the lake (instead of walking around it) is probably a bad idea.
January 20, 2011 at 5:31 am
The most basic solution is using the sp_send_dbmail stored procedure.
Read the contents of the Excel with an OPENROWSET query and e-mail the results directly to the recipients.
A more flexible approach is using SSRS, as already has been mentioned. Here you can control nicely the layout of your e-mail.
And of course you can do it in SSIS.
Keywords: excel connection manager, send mail task
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply