Packages not executing right on production server?

Hi,

I have developed about 20 to 30 packages that are executed on a specific order by a parent package that iterates a foreach cycle...

Has in development server all packages run just fine... when deployed to the production server and executed the first child package to run gives error and says that the logging text file was unable to find the file? It was supposed for the log to create and send information to the file right? So i think this is a little bit weird...

Any idea of what is going on here?

Regards,

Does the lggoing file folder exists, and does the execution security context (account) used have permissions to write to that folder? Different machines, so maybe the drives and folders are different, and your may well be using a different security context.|||

Where should i see the security context?

Anyway i have done the directory full previledges to "everyone" and nothing... one of the tasks creates the log just fine... but the other don't... the directory exists and i can't find a reason for it not create the file......

|||

The security context is the user who executes the package. Packages still execute on the machine which hosts the calling process, so were you are running DTEXEC or Visual studio, and they run under the security context of the user running the host program.

For scheduled jobs this is the SQL Server Agent service account or proxy account depending on your setup.

|||

I have already checked and all machines are setup the same...

I'm using visual studio 2005 in the production machine now with the project open and i can't really figure out what this error comes from... It seems that the variables are sent to the child package after the ssis logging so when ssis logging wants to start beeing used it can't...

Imagine i pass a variable called SOLUTION_DIRECTORY and that variable setups many things the log file path is one of them... os if this variable comes after the start of logging then we have problems....

Does this happens? Regards

|||

It depends how you're setting it. If its via a configuration then it should occur before you start logging.

If you pass it via the command-line, I'm not so sure.

Try outputting the the connection string of the log file to a different log provider, perhaps the event viewer. Output the connection string using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

It really seems that the logging is starting first then the package configuration that receives the variables from the parent package... this is really stupid... damn!

I tried to setup the variable with a default value correct and it worked... so how will i overcome this problem? I really need to log onto files and the file destination is dynamic... :( !!!

|||

I do a very similar thing. I use an XML config to set a variable to contain a path to my rootfolder and then dynamically build the logfile connection string from that. Exactly as you are doing.

This works fine. I'd be very very surprised if parent configurations occur at a different time.

-Jamie

|||

Has for what it seems it really is this way...

How can you explain the fact that if i set the variable in the child package to the right path it works and if not and pass the right path by the parent package it doens't? At least it can be very weird indead...

Another thing... is there anyway to validate if a directory already exists before creating it without using a script?

OUTPUT:

Error: 0xC001404B at CCCTT000D, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070003.

The system cannot find the path specified.

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x0 at Script Task: ligacao: CCCTT000D_<EMPRESA> UNL connectionstring: D:\CMSINTRABI\CG\DADOS\200401\CCCTT000D_CMS.UNL

Information: 0x0 at Script Task: ligacao: CONNECTION connectionstring: Data Source=CANARIO;Initial Catalog=CMSINTRACG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Information: 0x0 at Script Task: ligacao: FICHEIRO_LOG connectionstring: D:\CMSINTRABI\CG\LOGS\200401\CCCTT000D_CMS_CT.LOG

Has you can see the log gives error first then the variables are caught... :(

Regards,

|||

Yes its confirmed... it really seems that the logging starts first then the package configuration...

My text file for the ssis logging has this path configured in expressions:

@.[IN::SOLUTION_DIR] + "\\"+ @.[IN::MODULO]+"\\LOGS\\" + (DT_WSTR,6)@.[IN::PERIODO_CURRENTE] + "\\" + @.[System::PackageName] + "_" + @.[IN::COD_EMPRESA] +"_"+ @.[IN::STEP] +".LOG"

Well, all these variables come straight from the parent package.... if i don't set them with a default value the package will blow... but it i set them with an existing path it works just fine...

So this is it... Now WHY the hell don't the package configuration run in first place?

How will i make the logging this way? I really need help here! :(

Regards,

|||Can you not use a single configuration file and set it on all packages. It can supply the values you require to each and every package, rather than using the parent package variables. Configurations do happen before validation and execution. What is the problem with configurations you mention?|||

Well imagine that the parent package runs childs packages and send paremeters to each package...

Imagine for instance i run all packages by year month and i want the child packages to log the file to a directory somewhere in the corresponding year month... Ex: C:\logs\200604

but if that directory doenst exists at first then the error occurs... if the directory already exists then it works just fine :)

Basically i must be shure that the first directory setup must exist...

sql

Package trasfer to remote server

I have developed my ssis packages on my local system.Now i want to move them to a remote server.I have connected to the remote server over vpn.Now how do i transfer them to the server.Once i transfer i need to make few changes to the connection.What is the best way to transfer and make it work.

And then i want to schedule the packages to run automatically.

Please let me know

My first suggestion would be to try to read some of the books on-line (BOL). However, barring that, you can transfer the packages in one of a few different ways depending on how it is that you would like to store them on the remote server.

1.

a) You can simply copy the package .dtsx files to a folder on the remote server (or any remote folder that the user account running the package has access to, more on that later).

b) If you are storing it on the msdb you would connect to your ssis server (follow the following link for more information on setting up the dcom correctly to allow you to connect http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx) and then click through the stored packages -> msdb and create a new folder, then right click and press import package. The rest should be pretty self explanatory.

2. What type of changes do you need to make? If they are simply connection string changes and / or variable changes you can use an xml config file, the /set values tab of the job step maintenance screen or the data connections tab (for changes to the connections). I would suggest the xml config file option. If you need to open the package and edit, you can easily do this (provided you set the correct package level protection, i.e. encrypt w/ password or do not encrypt sensitive, etc) by simply opening the package from the remote server.

3. See the following threads / technet articles on setting up a job / credentials / proxy etc.

http://support.microsoft.com/kb/912911

http://support.microsoft.com/?kbid=918760

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2068280&SiteID=1

Let us know if you have any more questions or need any more help

Package fails with 0xC0047062

Background:
I have a package that reads data from an SAP table and populates a SQL table.
The package was developed using a custom source component I created to connect to SAP. The package runs with no errors in the Business Intelligence Development Studio.
I deployed the package to my test SSIS server successfully using a package password.
The Run Package option runs the package with no errors.

Problem:
I created a Job under the SQL Server Agent and configured a single Step to execute my package. This fails with the following error:

Code: 0xC0047062
Description: System.InvalidCastException: Unable to cast object of type ...
Description: component "SAP DB Source" (281) failed validation

The cast is fine as far as I can tell, so I'm not sure why this error occurs. I found other reports of this strange error message, but no solution. Any help is greatly appreciated!

Philip

Hello,

Have you ensured the custom components and dependencies are deployed on the target server besides the package?

sql

Package fails when run under SQL Server Agent

Hi,

I have a SSIS package that I developed using Business Intelligence Development Studio. I imported it into our SQL Server database and it runs fine (when I connect to Integration Services within SQL Server Management Studio, and run the package). But when I created a SQL Server Agent job comprising 1 step, to run this package, it fails. The failing step attempts to read data from a MS Access 2000 DB into SQL Server 2005.

I added logging to the package, and got the following error logged to the Event Viewer:
"The AcquireConnection method call to the connection manager "CurrentYearPCStats" failed with error code 0xC0202009."

I then created a new Operating System (CmdExec) step, calling DTEXEC with the command line copied from that generated by the SSIS step. The command line is:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\AFAReduction\AFA Reduction Integration" /SERVER <ourservername> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This failed with the same error in the Event Viewer, but for some reason also produced a meaningful error in the SQL Server Agent job's View History:
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\\<anotherservername>\PCStatslive\CurrData.mdb'. It is already opened exclusively by another user, or you need permission to view its data."

Please note that the SQL Server Agent runs using user id sqlservice, by default, and I have amended this user's Windows account to make it a Domain Admin.

Please let me know why I might get this error, when the package runs OK on its own.

Many thanks,
Keith.

The jobs running with the sql agent usually run with the agent's service account identity (local system or Network service).

This user must have permissions to open your Access DB files (NTFS security etc included).

When you run it from SQL mgmt studio it is running using your own credentials...

Hope this helps.

|||In addition to Zoran's advice, take a look at this KB article which describes common problems with running in Agent, and how to troubleshoot them:
http://support.microsoft.com/kb/918760|||

i have a similar kind of situation As a inidividual package or executing through the command line the package runs fine but when i try to execute it through the Agent it fails.

Executed as user: SEMASTER\Administrator. The package execution failed. The step failed.

Any help

Thanks,

Jasmine

|||

Hi again,

Thanks for your replies. You could've knocked me down with a feather - I came in this morning and the SQL Agent job that runs at 6 AM had succeeded! Tried manually and its all working fine! I've changed nothing, I have the Job History to prove that the last test I did yesterday failed, and everything since 6 AM today has succeeded.

The only thing I can guess is that there was some problem with the Access database itself, which cleared overnight. I'll keep an eye on this.

Thanks again,

Keith.

Package fails when ran as a scheduled job

I have a large number of SSIS packages, which I have developed over the last few months.

Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.

I am having real troubles with packages that move tables from one database to another.

I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.

One package which will not run at all, basically just moves 15 tables from database A on my server to database B.

The package is essentially a few SQL tasks to create tables, then a data flow.

The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.

In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:

Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error

I've googled for most of the error messages there, and tried applying some of the things I found.

I've set the commit size on the OLE DB destination to 10k rows.

I've set the number of engine threads to 2.

The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.

I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Matthew McNally wrote:

I have a large number of SSIS packages, which I have developed over the last few months.

Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.

I am having real troubles with packages that move tables from one database to another.

I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.

One package which will not run at all, basically just moves 15 tables from database A on my server to database B.

The package is essentially a few SQL tasks to create tables, then a data flow.

The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.

In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:

Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error

I've googled for most of the error messages there, and tried applying some of the things I found.

I've set the commit size on the OLE DB destination to 10k rows.

I've set the number of engine threads to 2.

The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.

I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Every time I have received this error:

"Communication link failure".

It has been due to network connectivity isuues during the execution of my package; nothing to do with the package itself...not sure if that is always the case.

|||I dont think its a network problem Rafael

Firstly, as there should be no network traffic, as the source and target database are on the same server.

Secondly, the package will consistently fail as a scheduled task. I could run it now manually - and it would be fine. Schedule it to run 2 minutes later - and it will fail.

Actually - I only assume there is no network traffic. The server is called Llama, and my package has two data-sources, both of which are mapped to databases on Llama.

Surely this does not mean that these packages are sending data across the network to talk to the server they reside on?|||

Verify your boot.ini file on that server and make sure that there is no memory limit like using /3GB flag. That will reduce available memory to outside applications. We faced similar issues on "Communication link failure". After removing /3gb limit flag it was running fine.

Best of luck.

Veera Maganti

|||Thanks for your response Veera.

the boot.ini on this server does not have the /3gb switch

The whole boot.ini is:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut
|||

Well there's your problem right there. Your server is called "Llama". if you upgrade to the "Cheetah" it will run just fine.

just joking... did you try entering a password into the SSIS to encryptsensativedatawithpassword? then schedule it and enter the password in there?

|||

Hi Matthew,

I have the same error, I want to know if you can solve this problem.

Thank you for your help.

Antonio

Package fails when ran as a scheduled job

I have a large number of SSIS packages, which I have developed over the last few months.

Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.

I am having real troubles with packages that move tables from one database to another.

I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.

One package which will not run at all, basically just moves 15 tables from database A on my server to database B.

The package is essentially a few SQL tasks to create tables, then a data flow.

The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.

In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:

Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error

I've googled for most of the error messages there, and tried applying some of the things I found.

I've set the commit size on the OLE DB destination to 10k rows.

I've set the number of engine threads to 2.

The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.

I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Matthew McNally wrote:

I have a large number of SSIS packages, which I have developed over the last few months.

Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.

I am having real troubles with packages that move tables from one database to another.

I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.

One package which will not run at all, basically just moves 15 tables from database A on my server to database B.

The package is essentially a few SQL tasks to create tables, then a data flow.

The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.

In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:

Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error

I've googled for most of the error messages there, and tried applying some of the things I found.

I've set the commit size on the OLE DB destination to 10k rows.

I've set the number of engine threads to 2.

The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.

I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Every time I have received this error:

"Communication link failure".

It has been due to network connectivity isuues during the execution of my package; nothing to do with the package itself...not sure if that is always the case.

|||I dont think its a network problem Rafael

Firstly, as there should be no network traffic, as the source and target database are on the same server.

Secondly, the package will consistently fail as a scheduled task. I could run it now manually - and it would be fine. Schedule it to run 2 minutes later - and it will fail.

Actually - I only assume there is no network traffic. The server is called Llama, and my package has two data-sources, both of which are mapped to databases on Llama.

Surely this does not mean that these packages are sending data across the network to talk to the server they reside on?|||

Verify your boot.ini file on that server and make sure that there is no memory limit like using /3GB flag. That will reduce available memory to outside applications. We faced similar issues on "Communication link failure". After removing /3gb limit flag it was running fine.

Best of luck.

Veera Maganti

|||Thanks for your response Veera.

the boot.ini on this server does not have the /3gb switch

The whole boot.ini is:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut
|||

Well there's your problem right there. Your server is called "Llama". if you upgrade to the "Cheetah" it will run just fine.

just joking... did you try entering a password into the SSIS to encryptsensativedatawithpassword? then schedule it and enter the password in there?

|||

Hi Matthew,

I have the same error, I want to know if you can solve this problem.

Thank you for your help.

Antonio

Package fails when ran as a scheduled job

I have a large number of SSIS packages, which I have developed over the last few months.

Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.

I am having real troubles with packages that move tables from one database to another.

I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.

One package which will not run at all, basically just moves 15 tables from database A on my server to database B.

The package is essentially a few SQL tasks to create tables, then a data flow.

The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.

In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:

Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error

I've googled for most of the error messages there, and tried applying some of the things I found.

I've set the commit size on the OLE DB destination to 10k rows.

I've set the number of engine threads to 2.

The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.

I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Matthew McNally wrote:

I have a large number of SSIS packages, which I have developed over the last few months.

Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.

I am having real troubles with packages that move tables from one database to another.

I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.

One package which will not run at all, basically just moves 15 tables from database A on my server to database B.

The package is essentially a few SQL tasks to create tables, then a data flow.

The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.

In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:

Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error

I've googled for most of the error messages there, and tried applying some of the things I found.

I've set the commit size on the OLE DB destination to 10k rows.

I've set the number of engine threads to 2.

The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.

I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Every time I have received this error:

"Communication link failure".

It has been due to network connectivity isuues during the execution of my package; nothing to do with the package itself...not sure if that is always the case.

|||I dont think its a network problem Rafael

Firstly, as there should be no network traffic, as the source and target database are on the same server.

Secondly, the package will consistently fail as a scheduled task. I could run it now manually - and it would be fine. Schedule it to run 2 minutes later - and it will fail.

Actually - I only assume there is no network traffic. The server is called Llama, and my package has two data-sources, both of which are mapped to databases on Llama.

Surely this does not mean that these packages are sending data across the network to talk to the server they reside on?
|||

Verify your boot.ini file on that server and make sure that there is no memory limit like using /3GB flag. That will reduce available memory to outside applications. We faced similar issues on "Communication link failure". After removing /3gb limit flag it was running fine.

Best of luck.

Veera Maganti

|||Thanks for your response Veera.

the boot.ini on this server does not have the /3gb switch

The whole boot.ini is:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut

|||

Well there's your problem right there. Your server is called "Llama". if you upgrade to the "Cheetah" it will run just fine.

just joking... did you try entering a password into the SSIS to encryptsensativedatawithpassword? then schedule it and enter the password in there?

|||

Hi Matthew,

I have the same error, I want to know if you can solve this problem.

Thank you for your help.

Antonio

Overcoming the connection limitations of MSDE....

I have a Windows application I have developed in VB using a MSDE
database. I will be distributing this to clients, most of which will
have only about 2-4 terminals, but there are many that will have 10-15
terminals running my application. I am afraid of what the performance
is going to be like with clients running a high number of terminals
because of the limitations of MSDE. I looked into the price of the
full version of SQL server for clients with a larger number of
terminals but it would cost way to much so that simply is not an
option for any of them.
I have spoken to other companies who develop products like mine and
they say they have clients running close to 30 terminals off of MSDE
with no slow down. They say they have done this by using connection
pooling. I have looked around on this issue and most people post
articles on connection pooling and the MSDE workload governor but I
have not seen any practical examples on specifically how to setup a
client application to overcome the MSDE workload limit.
As far as connection pooling is concerned, how exactly will this help
in limiting the workload? Does anyone have an specific examples of
how I would do this? I don't want to keep looking around and having
to experiment around with things that may or may not work because I
have no real way of testing this other than giving it to clients to
test. I'd really like to see an actual example of how someone has
done specifically what I'm doing here and has gotten it to work. I'm
surprised there are not more articles on this specific subject, but I
could not find any.
Any help would be appreciated, thanks...
On 15 Apr 2004 08:45:47 -0700, bostonpartykid@.yahoo.com (Ray Lavelle)
wrote:

>I have a Windows application I have developed in VB using a MSDE
>database. I will be distributing this to clients, most of which will
>have only about 2-4 terminals, but there are many that will have 10-15
>terminals running my application. I am afraid of what the performance
>is going to be like with clients running a high number of terminals
>because of the limitations of MSDE. I looked into the price of the
>full version of SQL server for clients with a larger number of
>terminals but it would cost way to much so that simply is not an
>option for any of them.
>I have spoken to other companies who develop products like mine and
>they say they have clients running close to 30 terminals off of MSDE
>with no slow down. They say they have done this by using connection
>pooling. I have looked around on this issue and most people post
>articles on connection pooling and the MSDE workload governor but I
>have not seen any practical examples on specifically how to setup a
>client application to overcome the MSDE workload limit.
>As far as connection pooling is concerned, how exactly will this help
>in limiting the workload? Does anyone have an specific examples of
>how I would do this? I don't want to keep looking around and having
>to experiment around with things that may or may not work because I
>have no real way of testing this other than giving it to clients to
>test. I'd really like to see an actual example of how someone has
>done specifically what I'm doing here and has gotten it to work. I'm
>surprised there are not more articles on this specific subject, but I
>could not find any.
>Any help would be appreciated, thanks...
Don't keep your connection open. Open a connection, get the desired
data and then close the connection. Open a connection, update/insert
the desired data and then close the connection. If your app doesn't
do really intensive data access and you aren't keeping the connection
open, chances of having more than 5 users hit the DB at the same time
are pretty slim.
HTH,
Bryan
__________________________________________________ __________
New Vision Software "When the going gets weird,"
Bryan Stafford "the weird turn pro."
alpine_don'tsendspam@.mvps.org Hunter S. Thompson -
Microsoft MVP-Visual Basic Fear and Loathing in LasVegas
|||hi Ray,
"Ray Lavelle" <bostonpartykid@.yahoo.com> ha scritto nel messaggio
news:d8653140.0404150745.2f9911ec@.posting.google.c om...
> I have a Windows application I have developed in VB using a MSDE
> database. I will be distributing this to clients, most of which will
> have only about 2-4 terminals, but there are many that will have 10-15
> terminals running my application.
> CUT
as Bryan already pointed out, keep your transactions as short as possible...
it's not a matter of connections, becouse users are "usually" idle, and
connection pooling is on by default, but can be not that useful, becouse
connections in the pool can be reused only if the connection string is exact
the same as the last used...
but, again, keep your batches as short as possible.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ray I have one question though about your scenario as I am in the same boat as you are...
How do you plan to deploy your app with MSDE. I know Microsoft has dismissed merge modules way and is advising the bootstrapper. I am still investigating the bootstrapper but was wondering how will you be setting up your desktop app (I believe it is des
ktop app) so that the data (MSDE database) is on shared server. Are their any specific customizations that you are doing to the setup. Do you have any code for this which you can post for all...
Thanks a goodluck
dev
|||The number of connection that you can have with MSDE are the same as SQL
Server (32767) . The limits in MSDE are related to the amount of work that
it can do at any given time and the size of the database. If your clients
are not hammering the database server with constant requests then it is
certainly possible to scale to possibly hundreds of connections. For
information about how this workload governor works see this link:
http://msdn.microsoft.com/library/?u...asp?frame=true
Your application should only hit the database when it needs to and you
should strive to get all the data you need for a function of your
application in one request. Use stored procedures to handle the logic of
what data elements you need; rather than getting a piece of data returning
it to the application, deciding you need an additional pieces of data and
then fetching them repeatedly from the database.
We have customers using MSDE with our application and running many client
workstations with no problems. On the other hand, many of our customers
already have one or more installations of SQL Server on site which they
install our database to and are able to scale to dozens of workstations.
Jim
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0404150745.2f9911ec@.posting.google.c om...
> I have a Windows application I have developed in VB using a MSDE
> database. I will be distributing this to clients, most of which will
> have only about 2-4 terminals, but there are many that will have 10-15
> terminals running my application. I am afraid of what the performance
> is going to be like with clients running a high number of terminals
> because of the limitations of MSDE. I looked into the price of the
> full version of SQL server for clients with a larger number of
> terminals but it would cost way to much so that simply is not an
> option for any of them.
> I have spoken to other companies who develop products like mine and
> they say they have clients running close to 30 terminals off of MSDE
> with no slow down. They say they have done this by using connection
> pooling. I have looked around on this issue and most people post
> articles on connection pooling and the MSDE workload governor but I
> have not seen any practical examples on specifically how to setup a
> client application to overcome the MSDE workload limit.
> As far as connection pooling is concerned, how exactly will this help
> in limiting the workload? Does anyone have an specific examples of
> how I would do this? I don't want to keep looking around and having
> to experiment around with things that may or may not work because I
> have no real way of testing this other than giving it to clients to
> test. I'd really like to see an actual example of how someone has
> done specifically what I'm doing here and has gotten it to work. I'm
> surprised there are not more articles on this specific subject, but I
> could not find any.
> Any help would be appreciated, thanks...
|||Jim,
Just curious, how do you change the connection string to specify where the database as some of your clients use MSDE and some use SQL Server.. does it happen during the installation. Also do you prompt them during installation on whether they want to ins
tall MSDE or use SQL Server they already have...
thanks
dev
|||You'd be much better off just disabling the workload governor. It's really
easy to do, all you need is change one byte in a hex editor.
Here's the method:
1. The file you will be changing is SQLBOOT.DLL. This can be found in
Microsoft SQL Server\MSSQL\Binn. The rest of this method only applies to
version 2000.080.0194.00 of the file, as included in Service Pack 3a.
Check the version of the file by looking at the Version tab of its
Properties window, and the size of the file, which should be 33,340 bytes.
You may also want to check the MD5 sum of the file, which is
175b236765fb446f46da5da635681ab8.
2. Obtain a suitable hex editor from somewhere. A fairly decent one that can
do the job is available here:
http://www.catch22.org.uk/software/hexedit.asp
3. Make a backup copy of SQLBOOT.DLL somewhere, just in case.
4. Stop MSDE's MSSQLSERVER service.
5. Open the original SQLBOOT.DLL in the hex editor.
6. Find the byte at location 10145 decimal / 0x27A1 hexadecimal.
7. The current value of the byte should be 08. Change it to 00.
This changes the target benchmark users / concurrent workload limit value
from eight to zero. When the SQL Server reads this as being zero, it
disables the workload governor.
8. Save SQLBOOT.DLL.
9. Start the MSSQLSERVER service.
If it worked, the SQL statement "DBCC CONCURRENCYVIOLATION" should return
without any output (normally it would display a summary of when the workload
governor has been active.)
Hope this helps!