Problem scheduling processing

When I manually run the IS packages to load the data warehouse and then process the dimensions and cubes, everything works fine.

When I schedule the packages as jobs, eventually the data goes wrong. The jobs are scheduled in the correct order.

Any ideas?

Thanks.

If I get your description right, the SSIS packages are not executed in the correct order. Is that so?

For one you can use serveral other ways to process your dimensions and cubes: You can use ascmd command line utility to process exactly the cubes and dimensions. Then wou can schedule a SQL agent job to run ascmd.

You can also use SQL Agent jobs directly to send processing commands to Analysis Serivces.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

eventually the data goes wrong.

What do you mean by "goes wrong" is it possible that it is just not updated, or not fully updated?

One common trap when scheduling jobs is that usually the scheduled instance runs under a different user account. You should double check that this account has all the appropriate priviledges. Sometimes logging on to your workstation with the same account that the SQL Agent is running under and running the packages interactively can help to highlight any issues. You might find things like network drive mappings are not the same, or privileges to access other databases are not the same your own account, or even that the account in question does not have rights to process the SSAS database.

Problem scheduling DTS packages

I am getting the following error whenever I schedule a new DTS package:
SQL Server does not exist or access denied
This is new, all of my older scheduled packages run without any problem.
This just started occuring during the past week and is occuring on all of my
SQL Servers (7 and 2000).
Any ideas?John
Well , Perhaps you are created the DTS package on your computer and when
you execute the package it runs under account of your computer (SQL Agent
service)
By default, the owner of that service is LocalSystem (with has many
Administrator-style permission, but *NO* access to any network resources).
So, change the account of that service to a Domain Admin account (or other
suitable account with permissions on both computers (server and yours)
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> I am getting the following error whenever I schedule a new DTS package:
> SQL Server does not exist or access denied
> This is new, all of my older scheduled packages run without any problem.
> This just started occuring during the past week and is occuring on all of
my
> SQL Servers (7 and 2000).
> Any ideas?
>|||I'm just curious why it was working, I haven't changed anything and now it
does not. The network guys applied some security patches about the time
this started.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eERZIZVnDHA.3612@.TK2MSFTNGP11.phx.gbl...
> John
> Well , Perhaps you are created the DTS package on your computer and when
> you execute the package it runs under account of your computer (SQL Agent
> service)
> By default, the owner of that service is LocalSystem (with has many
> Administrator-style permission, but *NO* access to any network resources).
> So, change the account of that service to a Domain Admin account (or other
> suitable account with permissions on both computers (server and yours)
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I am getting the following error whenever I schedule a new DTS package:
> >
> > SQL Server does not exist or access denied
> >
> > This is new, all of my older scheduled packages run without any problem.
> > This just started occuring during the past week and is occuring on all
of
> my
> > SQL Servers (7 and 2000).
> >
> > Any ideas?
> >
> >
>

Problem Related to Package execution in SSIS

Hi,

I have the hierarchy of packages..

there are three levels..

first package (say L1) calls two child packages.(say L2_1 , L2_2)

the parameters are passed to L2_1 from L1 and it executes completely and successfully.

next step is L1 calls L2_2 without any parameters.. and package fails with error as execution of L2_2 failed.

when I executed L2_2 individually it successfully executes.

L2_2 itself calls 5 other packages (at level 3)..

Am not able to identify the possible reason for failure..

I suggest you turn on package logging in each package (right click on the control flow and select logging) and then come back here with specific error messages once captured.|||

Thanks for you help phil...

now I can see the errors due to which my package is failing..

sql

Problem Pulling From Access DB

We are currently moving existing SSIS packages from one server to another. The former server is 32 bit and the new server is 64 bit clustered (not sure if that's relevant or not, though).

One package in particular is giving me a headache. It pulls from an access file to sql server 2005 table. We set up the security as don't save senstive and put the connection strings in a config file. We had to set the original job up to run the package step under a proxy account to get it to work.

If we right click on the package through the SSIS Store interface and execute it runs fine. However when we try to run it through the scheduled job, it fails. I very much appears to be a permissions issue on the proxy account. The person setting up the server is somewhat new to the area of SQL Servers, so they cannot provide much feedback. We're very much in a tweak until it works position, unfortunately.

Here are the errors that I am getting, one from the history of the job, one from the SQL Server logging. Does anyone have a suggestion, and if it is a permissions issue, where to look? I've been at this for days, so I've tried several different approaches. It seems like right now that I almost got it to work, but it seems like the connection to the access db is failing. But if that's the case, why does it run fine through the proxy on the original server (using same account)? I guess that very well could come down to the 64 bit problem (saw this on some other posts). I should mention that I set up the package as Run64BitRuntime = false. Do I need to rethink this as a batch file using DTExec? That was actually our original solution on the former server, but we could never get to work.

Job History Error:

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-03-23 16:19:14.92 Code: 0xC0047017 Source: Data Flow Task DTS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2007-03-23 16:19:14.92 Source: Data Flow Task Validating: 100% complete End Progress Error: 2007-03-23 16:19:14.94 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-03-23 16:19:14.94 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation.... The package execution fa... The step failed.

sysdtslog90 error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

OK I solved the problem myself. This was in fact a 32-bit vs. 64-bit problem, even though the lovely errors I was getting didn't even come close to explaing that to me. I provided a link below on how to run a package in a scheduled job that hits a Jet 4.0 connection (I think excel may have this problem also).

But, I hate when people simply do that (include link), so here's how I fixed it.

Create a new scheduled job.
Add a new step.
When you edit the step, the 'Type' will be "Operating System (cmdExec)"
You can use DtExecUI to create your command line step, but here is what I used exactly - quotation mark locations are VERY important. Also, I think you may need to have the complete call on one line:

\\Server\E$\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe /DTS "\MSDB_ATC\PACKAGE_NAME" /SERVER "SERVER_NAME" /CONFIGFILE "\\Server\R$\\PACKAGE_CONFIG.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Edit command to match yours, and paste into job step edit.

Hope this helps someone, it took me DAYS to figure out.

http://msdn2.microsoft.com/en-us/library/ms141766.aspx

|||

thank you... It's realy helpful to me.

The following is my update list,

\\Server\E$\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS "\MSDB_ATC\PACKAGE_NAME" /SERVER "SERVER_NAME" /CONFIGFILE "\\Server\R$\\PACKAGE_CONFIG.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

|||Thanks Jay, this saved my day

Problem opening MSDB folder under Stored Packages

I am able to connect to Integration Services in MSSMS. However, when I try to expand and click on MSDB under Stored Packages, I get a Login Timeout expired error. I get this error both locally, and remotely. Error message suggested "does not allow remote connections", but I checked Surface Area Configuration, and remote connection is already enabled. Moreover, I get the same error even when connecting locally.

any ideas?
Do you have a default instance of SQL Server installed in that machine?|||No, it's a clustersql

Problem of Parent-Child package with ASP.Net

I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.)

I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start.

Could somebody explain why this is happening? any suggestions/ similar experiences?

Regards

Saurabh

One more observation: if i run the same parent package as a stand-alone. (i.e. not through the asp.net application) it always executes fine. it calls all the child packages very well always. what is different in the asp.net context?