How to schedule a job that runs an SSIS Package in SQL Server

Again I just want to document how I did it. It's hard to remember sometimes the steps taken to make things work. Specially for things that are not done everyday. In this particular case, it is confusing which user accounts or login to specify to be used by the server agent. Anyway, here's a recipe on how to make this work, just follow along...

1. Create a credential.
2. Create a proxy under SSIS Package Execution using the above credential.
3. Create a job in SQL Server Agent, and this is the important part, specify "sa" as the owner of this job.
4. Now add a Step, select SQL Server Integration Services Package type. Again this is the crucial step: Go to the Run as dropdown, then select the proxy specified above.

Now test your package and let me know if it works for you.

How to import SSIS package protected by password to Integration Services

Ok, so that I can remember how to do this again. When importing SSIS package from development machine to server and the package protection level is "Encrypt sensitive data with password", this is for example, when you have a package that queries from an Oracle database, and you wanted to encrypt your credentials. I always forget to specify the package protection level. I don't know if it's because of me or the UI, the Protection level textbox is grayed out so one would normally assume that it's not needed. Then one would think that everything is ok until the package is executed, and you wonder why it errors out. So anyway, so I don't forget and to save me time in finding errors that traces back to not specifying passwords properly, I'm taking these notes on how to specify package protection level...

1. In Stored Packages, right click in MSDB then select Import Package...
2. For Package Location, select File System.
3. Specify Package path and Package name.
4. Then click the ellipsis button next to the Protection level textbox...

5. Now select "Encrypt sensitive data with password", then enter password...

The breakpoint will not currently be hit

This is so annoying. I'm using Visual Studio 2005, and for some reason I cannot set breakpoints to do my debugging. I tried several suggestions I can find from the net but still it doesn't work. Since I don't know the solution yet, but will expand this writeup once I figure out. But for me to debug on my ASP.NET Project right now, I added the following in my code. Important thing for me now is to debug my project.

System.Diagnostics.Debugger.Break(); // DEBUG

Constructor Dependency Injection Design Pattern

Note on Constructor Dependency Injection design pattern. This is how it is in C#.

private IContactManagerRepository _repository;

 

public ContactController()

    : this(new EntityContactManagerRepository())

{ }

 

public ContactController(IContactManagerRepository repository)

{

    _repository = repository;

}

Perl Design Patterns

Lookin' at Design Patterns for Perl. Here's the link.

Configuring Job Property Notifications for Sql Server 2005

I spent so much time figuring out how to configure Notifications in SQL Server 2005 job. One of the properties that can be enabled in a Job is "Notifications", when the job succeeds or fails or completes. I wanted to know when the Job I setup fails and so I set it to my E-mail as well as "When the job fails". But one thing about SQL Server Management Studio is that it doesn't tell you if you're lacking some more configurations. I wished it prompted me that I needed to do some other settings for it to work. Anyways, below I just wanted to outline what I did to make this work.

1. Setup Database Mail. Follow this.
2. Setup a Sql Server Agent Operator. Follow this.
3. Now Restart SQL Server Agent.

Thank you for those who wrote the posts I followed above.