Hi,
Some of you guys seem to be gurus with the new Integration Services technology, so I hope someone can lend me some advice, as I haven't worked much with integration before.
Ok, here we go. What I want to do is select some data from my database and export the result to a flat file (really a .csv file with values delimited by semicolons). Sounds like a simple thing to do Well, I'm sure it is when you know how to do it :) I know I could manage the same thing by writing a C# class that creates that .csv file, but the decision has been made to use Integration Services for these kind of operations.
I created an SSIS project in Business Intelligence Development Studio, and created a package (I defined the task flow etc.). By choosing "Execute package" from the IDE I managed to create the flat file, and everything seemed sweet. However, When trying to execute the package (package.Execute();) from C# code, it only results in a failure. I have read on several sites that this has to do with my program lacking the rights to run the package from the client side. OK, fair enough. I need to create the package on the server, and use an SQL Server Agent to execute the package through the agent.
Can anyone tell me how I need to do this How can I ensure that the package is created on the sql server instead of locally on my development computer When I create a new SSIS project the package is already made, and it is created locally on my PC.
I hope someone can give me some help. Even a little nudge would be appreciated ;)
Thanks in advance!
Greenies
Create the package on the server, and execute through sql server agent
moveit
Greenies,
You did good by enabling the logging of the step job, but I don't think that fixed your problem. When you use CmdExec you should get more details of the error in the same place you got them when using SSIS package step. Most of the issues when running a package via SQL Agent are related to permission of the account running the package or the protection level of the package. If you do a search with SSIS packages and SQL Server agent as criteria you will find a lot of information. I founf these 2:
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step
http://support.microsoft.com/default.aspx/kb/918760
How to use the dtutil utility (Dtutil.exe) to set the protection level of a batch of SQL Server Integration Services (SSIS) packages in SQL Server 2005
http://support.microsoft.com/default.aspx scid=kb;en-us;906562
Rafael Salas
Handerson
Greenies,
You may want to set the SQL Server agent job using CmdExec instead of Integration Services package in order to get more details in case of an error.
See more details here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html
Rafael Salas
HowardRichards
Firstly, here's a link that shows another couple of ways to execute an SSIS task from code. The web service may prove useful when calling from an ASP.NET application.
http://msdn2.microsoft.com/en-us/library/ms403355.aspx
If you look at the security and in particular your account which is running the agent.I think (not 100% sure) that you need to set user mappings on the msdb database so that the user has the role of:
SQLAgentOperatorRole
hopefully this should allow the user account to access and execute the SQL job.
Hope this helps,
Grant
nature0276
What the...
Since my last post I got it to work! The last thing I did was opening the agent job in SQL Server Management Studio, and clicked on the Edit Step button. I then chose the Logging tab, chose "SSIS Log Provider for SQL Server", and provided my connection manager which I had earlier specified in the package. I saved my changes, ran the code and VOILA! the destination table was filled with data and the history log informed me that the job had executed successfully.
Can anyone understand why the job wouldn't execute successfully without logging activated Is there a logical explanation behind this The important thing is that it now works, and I have taken a pill for my headache, but this was a frustrating ride to say the least.
I will write down every little thing I did in order to get this to work, since I will be using Integration Services for more tasks in the project I'm currently working on. Thanks to all who helped me get this to work. You are the best! :)
Greenies
Philly10407
Hi Rafael!
My user was given the "SQLAgentOperatorRole" on the msdb database, but that didn't help. I also tried changing the step type from Integration Services package to CmdExec, but I didn't get a more detailed error message than what I find in the history log.
Maybe I am looking in the wrong place for the log details. Where is CmdExec supposed to show me the error details
I have also tried enabling logging in the package, and then saving the package to the msdb database again (I overwrite the old package with the new copy), but my problem is the same: where is the log entries written to
My current error message, which I find in the history for the agent job is as follows:
"Executed as user: [MyInstance]\SYSTEM. The Package execution failed. The step failed."
I read another thread where another user had the exact same error message. He said he tried the CmdExec option, and it worked. Now I'm beginning to wonder what I did wrong when I tried changing the step type to CmdExec.
Another thing which I have been thinking about is that I have selected the protection level "Encrypt sensitive information with password." Could this be the problem When I start the job from my code I never specify the package password. Is that needed somewhere as a parameter to the "sp_start_job" procedure
I'm really starting to get an "Integrated" headache over all this... :)
Greenies
Jan Kučera
You need to create the job in the SQL agent section of SQL server. What Greg meant by adding the integration step is that when you click on the Steps option in the SQL Agent job wizard, there is a drop down list for type. You can select integration services package under this drop down. From that there are a number of other tabs that appear which allow you to set the data sources etc.
Hope this clarifies things for you.
Cheers,
Grant
Jasbir
Yes, thank you, Grant. I now see what he meant.
I have now managed to create the package, save a copy of it to the server, create the SQL Server Agent job, and I have set the step type to Integration services package. My challenge now is to manage to run this job from my ASP.NET page. I have written some code that doesn't return an error. But the destination table (I output to a table in my database for testing purposes, until I get the agent job to run properly) is not populated with data. By searching through the msdb database, I managed to locate the error in the "sysjobhistory" table. The error is as follows:
"The job failed. The Job was invoked by User [MyUser]. The last step to run was step 1 ([MyStep])."
MyUser is the name of the user that is the owner of my destination database, and has sysadmin rights (for the time being). MyStep is the step that I have defined in the SQL Server Agent job.
Obviously, the user I specify doesn't have the necessary rights to execute the job. MyUser has the rights to do almost anything to my destination database, but maybe I need someone with rights to do things in the msdb database. That leads me to the question: which rights does my user need to be able to run the job Is there a default user in the database that I can use, that has these rights
Thanks again for your help. It has helped me a lot so far.
Greenies
Dhaval Patel
Thank you for your reply, Greg. That was the kind of information I was looking for. However, I am unable to try it out just yet, because I'm currently waiting for my DB Administrators to grant me the rights to store the package in the msdb table. I guess that is what you mean by "Copy the package onto your server." There is an option in the file-menu that is called "Save copy of package as .dtsx" which I'm sure is what you are talking about. As it is now, I'm unable to click the Browse button beside the "Package Path" textbox (in the save copy of package dialog window).
You mention the creation of a SQL Agent job in the Integration Services Studio (SQL Server Business Intelligence Development Studio). Can I do everything related to the Agent job from within this studio, or do I need to create an Agent job on the SQL Server as well
Best Regards,
Greenies
Evan Mulawski
Copy the package (i.e. the .dtsx file) onto your server. Create a SQL Agent job with a Integration Services Package Step. On the Data Sources tab configure any connnection managers you need to, and set an variable values on the Set Values tab.
That's pretty much it. Then just run the job in Agent.
As an alternative, if you don't have logging enabled in the package, you can run it from the command line. Easiest way is to set up the package in SQL Agent, then look at the Command Line tab. Copy ALL this text. Then, from a command prompt type "dtexec " and paste the command line text from agent in.
Greg.
SIYER
The article that you are providing a link to is the one that I used for writing my test application. I use the top example, executing the agent job. I don't think our DBA would approve of us developers placing a WebService on the Database Server, so until a certain very hot place freezes over, I think I will have to find a way to get the agent job option to work.
I have tipped our DBA about the SQLAgentOperatorRole (or maybe the role I need is called something else), and I'm currently waiting for a reply from him. We're both quite fresh in the Integration Services playground, so at the moment we sort of walk around in the darkness while trying to locate the position of the light switch with outstreched arms :)
I will post another reply in this thread when I get a chance to test my application again with new privileges added to my user.
Greenies