September 15, 2015 at 10:18 am
If you all looked at my previous post, I was able to create a view and convert it to excel. Now I want to it to schedule it for everyday and then email the excel file as an attachment to couple of people.
Would SSRS be an option? where I can create a report of the view and schedule it?
Does any know the process I need to follow?
Do I have to uses SSIS ? and then set it up as SQl server job?
September 15, 2015 at 10:42 am
Hi
I think SSIS should be sufficient to do this.
You only need to create simple package with some components (Ole DB Source, Excel Destination and Send Mail Task).
It's enough to add the new job to Sql Agent and done.
Best Regards
Mike
September 15, 2015 at 11:07 am
On the SSIS when I run the OLE DB source to excel destination field it gives me an error 'cannot convert between Unicode and non uni code string data types'
Do you know how to solve this error?
September 15, 2015 at 2:06 pm
So I was able to convert it into a flat file, now I need to know how do I schedule it to send an email with the flat file attached.
September 16, 2015 at 1:16 am
Hi
The Excel driver recognizes only a limited set of data types, please refer https://msdn.microsoft.com/en-us/library/ms137643.aspx.
You can cast all char (varchar) datatypes to nchar (nvarchar) in your view or add Data Conversion Transformation between Ole DB Source and Excel Destination.
And as You wrote, there is another option, write data to a text file with Flat File Destination, small tip: set tab as column delimiter and
set xls extension in ConnectionString property instead of txt.
Best regards
Mike
September 16, 2015 at 1:33 am
Regarding scheduling: Please deploy your package in File System or save to the MSDB database on an instance of SQL Server.
Create the new job in Sql Agent service, you can find simple tutorial here: http://stackoverflow.com/questions/6712811/how-do-i-create-a-step-in-my-sql-server-agent-job-which-will-run-my-ssis-package.
Best Regards
Mike
September 16, 2015 at 8:24 am
Does the package has to be on the same server?
September 16, 2015 at 8:41 am
Hi
Job usually runs under SQL Server Agent Service account, it does not have access to network folder, of course you can set up a proxy with domain credentials to reach network path, but in this case you have to put a UNC path in order for it to work.
Br.
Mike
September 16, 2015 at 8:44 am
I am sorry but what would be the UNC path?
September 16, 2015 at 8:45 am
I also deployed the package to SQL server Integration services, and when I run the package from there it works also. when I schedule it, it gives me the following message.
Description: Connecting to the Integration Services service on the computer "XXXXXXX" failed with the following error: "Class not registered ". This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance. Source: Started: 9:40:44 AM Finished: 9:40:45 AM Elapsed: 0.032 seconds. The package could not be loaded. The step failed.
September 17, 2015 at 6:04 am
Hi
Have you got different SQL editions on Dev and Prod environments?
Br.
Mike
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply