How to schedule to run Integration Packages using SQL Agent/Job?

Hi Folks,

I deploying a couple of integration packages which needs to be run sequentially. Currently, I quite puzzled how come i cannot run them as a SQL Agent job. It always fail with "Executed as user: Servername\Administrator. The package execution failed. " But if I had put import these integration packages into the SQL Server 2005 Integration Services FileSystem, it does run but i need promptly as there is a dialog requesting whether to execute the packages. Is there a way to automate the running of these packages through SQL agent. ? Pls help need to get it up and running asap. Thanks first.

Yes there is. Most problems experienced when using SQL Agent to run packages are down to the package being run as a different user. Is that what you are doing?

-Jamie

|||Hi Garynkill23,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

Andy Leonard wrote:

Hi Garynkill23,

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Andy,

Why do you think that makes most sense? To me, it makes more sense to set ProtectionLevel=DontSaveSensitive and then store all your connection strings in configurations. Personally I think this should be considered best practice - its a matter of opinion of course.

-Jamie

|||

Hi Jamie,

I think the method you advocate (DontSaveSensitive) is great, and perhaps best.

My experience with this question drives my copy-and-paste answer about using a password. Here's what I've seen: Someone starts using SSIS and works through some issues as they scale the learning curve (reading good books and a great blog over at Conchango for help as they go).

Then they move it to a test or integration server, experiment with the job step type until they get a job to run when they right-click the job and click Start Job at Step... So they schedule it to run and wait. The SQL Agent scheduler fires the job and it fails with a "cannot acquire connection" (or similar) error. We know why but it seems completely baffling to them. (It certainly did to me when it first happened!)

What's worse is there's little or nothing in the log (the package cannot make that connection either).

While EncryptWithPassword method works, it also requires folks to keep track of a(nother) password.

I start here mainly because it's the quickest way I can think of to get that package up and running. Everyone understands passwords. I don't claim to be right or even advocating a best practice (at least not yet) - I'm just trying to help SSIS developers get that package running.

I welcome any feedback - from anyone. I'm still learning too! :)

Thanks,

Andy

|||

That all sounds fair enough to me Andy. I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations. As I aluded to, its all subjective.

Good discussion.

-Jamie

|||

Jamie Thomson wrote:

I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations.

I cannot argue with you. I advocate 2-pass (minimum) configurations to consulting clients. But I'm also right there to either implement it myself or show them how to implement it.

Jamie Thomson wrote:

Good discussion.

I couldn't agree more!

Thanks,

Andy

|||

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.

Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

|||

garynkill23 wrote:

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.

Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

As discussed above, if you have Protectionlevel=DontSaveSensitive then you will more than likely have to use configurations.

What do you mean by "put it under maintenance"?

-Jamie

|||

Hi garynkill23,

This is complicated. It touches a lot of moving parts in your SSIS package, SQL Servers, and enterprise domain. There's just no simple and easy explanation. But it is this way for good reason and that reason is to provide security.

I wrote a blog entry that talks about connections between SSIS and SQL Server - if you use a SQL Login to connect to SQL Server. This should also apply to storing credentials for any provider that requires a username and password.

Windows Authentication is simpler and safer provided your SQL Servers use one or more domain accounts for the SQL Agent service. (It also offloads SQL Server connectivity account maintenance to the Help Desk - at no extra charge.) If you use a domain account for the SQL Agent service, your SSIS connection managers can all be configured to use Windows Authentication to connect, and you simply grant the SQL Agent service domain account the access it needs in SQL Server. Does this make sense?

For example: I have a domain account named MyDomain\Andy. I log in as MyDomain\Andy and write an SSIS package that connects to a Dev SQL Server. I use the default ProtectionLevel: EncryptSensitiveWithUserKey. MyDomain\Andy has sufficient privileges on the Dev SQL Server. When I'm done, I can manually execute the package and it succeeds in connecting to the Dev SQL Server.

Next, I deploy the SSIS package to a Prod SQL Server. I create a SQL Agent job with an SSIS step that calls the SSIS package. I schedule the job to run at 2:00 AM. Again, MyDomain\Andy has sufficient privileges in the Prod SQL Server. When I right-click the SQL Agent job I just created, I can select "Start Job at Step..." and the job executes, calls the SSIS package, and both succeed.

What just happened? From the security context point-of-view, MyDomain\Andy just executed this job. This is important. The SQL Agent scheduler did not execute the job. In this scenario, that won't happen until 2:00 AM. Although this was a good test, it was incomplete.

To continue the example, let's assume the SQL Agent service on the Prod SQL Server runs under a domain account named MyDomain\SQLAgentService. When the scheduler fires the job at 2:00 AM, MyDomain\SQLAgentService will try to log into the Prod SQL Server and perform the operations specified in your SSIS package. If it lacks permissions to connect, the SSIS package will fail, causing the SQL Agent job to fail. You will get an error message similar to "failed to acquire connection". The error varies because the permissions granted MyDomain\SQLAgentService vary along with the configuration of the SSIS package:

If Logging is enabled in the SSIS package, it will probably attempt to connect to the logging provider before attempting to connect to a SQL Server - unless you're using a SQL Server logging provider. If it cannot connect to the Logging provider, it cannot log the fact that it encountered an error connecting to the Logging provider.

how to run the package trough the stored prcedure

hi focks;

through the jobs is possible it s working fine but

is it possible to run the packages through the stired procedure 2005

ok if possible how please help me

regards

koti

You need to use xp_cmdshell to launch DTEXEC.

How to run parallely/sequentially

I am having different packages under one project. Each package is having one one control flow, which internally contains one data flow. I want to run these data/control flows in one sequence. I am not able to drag all of these data/control flows into one sequence container. Could anyone tell how to do this in SSIS?

I would create a controller a package, and use Execute Package Tasks to call the existing worker packages. Do not constrain the Execute Package tasks and they will execute in parallel.

|||I didn't get it.

My question is how to run different packages sequentially or parallely. e.g I am loading customer dimension in one data flow(one package). Then I am loading parts dimension using one more package. Then I am loading Calendar Dimension using another package. How do I combine these in a single process flow/workflow, so that one flow can run after/with another .|||You haven't given any details of the workflow you want, so I'll just guess. If I have two dimension load packages and one fact load package I would load my dimensions in parallel, but would want my facts to load after all dimensions for example.

So I would create a new package, the controller package, and add an Execute Package Task to call my Dim 1 package. Then add a second Execute Package Task for my Dim 2 package. Then add a third Execute package task for my Fact package. Now drag and drop the workflow (green dangling lines) from the Dim 1 Exec Pkg Task to Fact Exec Pkg task. Repeat for Dim 2 to Fact.

So now the dimensions are unconstrained and will execute in parallel, but the fact will wait for both dimensions to complete successfully.
Dim1Fact
Dim2||||Thanks DarrenSQLIS, I got it|||The execute package task is working. but when I run my sequence container, which contains 3-4 execute package tasks--it opens up each and every pacakge on my screen (while running). what do I need to do, if I don't want to open them. I want to execute this sequence, without opening up each and every package..|||That's just what the debugger does. If you run it with dtexec (Ctrl-F5) that won't happen. But then you won't get them purty colors. sql

How to Run DTS Package from remote server with SQL 2005

I am new to SQL Server and need to understand how to run DTS packages (SSIS) in SQL 2005 from a remote server? In other words run them from another server from where the database is installed.

I am looking for any links on the subject, guidance on how to set this up and how to use it.

Regards,

Lee

You just have to have the ssis runtime installed on the system you want to run the packages from. As long as you can make a connection from that machine to your database machine you should be ok. And of course you also need the workstation components installed on whatever machine you're doing your dev work from.

If you want to store your packages in the msdb db you may run into a problem because the default is to use the default instance on the local machine to connect to the msdb. There's a file you can edit to change this but I can't remember it off the top of my head.

|||If I correctly understard the question, you have a server where you have installed SSIS and some packages, and you want to start these packages from a remote computer (client), right?

The usual way to set this up is to create Agent Job that will run the package, without any schedule - so it would not run by itself. Then when you need to run the package from the client, you execute Agent stored proc (via SQL) to start the Job.|||

Hi Mike,

Thanks for the information. I will share this with the other developers working with me on this.

Lee

sql