.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

ETL package runs locally but fails on server

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :ASP.Net


I am working on an ETL package which reads the data from text file, validates the data and puts it in relevant tables of the DB.

It was working fine until recently I had to make some changes in it. When run the package on local environment, everything worked fine.

When I uploaded the package on server and started testing it, it gave me error. I am passing information like ConnectionString, filepath through user variables. The package is executed by a windows service on server which passes these parameters to the package.

I double checked to see if the values in config file of the windows service are correct but unable to find what is wrong.

Can any one please give me a hint on this? 

View Complete Post

More Related Resource Links

SSIS package fails under SQL Server Agent job


Hi All

I have designed a SSIS package to encrypt a file using Cipher (EFS) it works fine from BIDS & Package Execution Utility.

But SQL Server Agent job is failing to do this. I am getting the error as

" In executing C:\Windows\System32\Cipher.exe /e /a C:\Test.txt at "",The Process Exit code was 1 while the expected was 0." 


Please help me.




SSIS package which reads excel files fails on SQL Server Agent



I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.

I tried all the possible fixes I found on the web but still can't get it to work.

Could you shed some light on it?



Package fails from Sql Server Job


Hi All,

I have a ssis pacakge which runs fine form the BIDS.I used a config file for this package.

When i run the same pacakge form Sql Job am getting the below err::


 Description: The connection "AnalysisDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.  End Error  DTExec: Could not import the configuration file H:\ProcessCube.dtsConfig.  Started:  2:53:44 PM  Finished: 2:53:45 PM  Elapsed:  0.281 seconds.  The package execution failed.  The step failed.

How can i debug this.Am using the sqlserver acc to run this.all the other packages run fine for this acc.

where can be the issue be?

SSIS package runs very slow on SQL 2008 R2 server, runs very quickly on virtual XP machine


Hi All,

I've been troubleshooting this issue on and off for a few weeks now.  I've scoured these forums and looked through sqlis.com, but I'm just about out of ideas.  Here's what's happening.

I have a *very* simple integration services package I developed on a  virtual Windows XP machine running on my workstation.  The package uses a script task to simulate a one-row source with four columns (int, char(64), varchar(512) and char(2)) and then passes that off to a fuzzy lookup.  The fuzzy lookup is running against a table of only about 6,000 rows.  The package then inserts the results into a table and exits.

When I run this from my virtual XP machine, it completes in 1-2 seconds.

When I execute this on the server (either as a job or through DTExec) it takes 14-18 seconds.  The server stores the package in MSDB and is also the location where the fuzzy lookup happens.

I've tried using the 32-bit runtime, creating 32- and 64-bit aliases to the server, changing the name of the server from the name to the IP address and port, stopping/restarting SQL Agent and SSIS Services... none of this seems to change the execution time.

The one thing I've found is that if I enable package logging on the logging tab of the job, using the SSIS Log Provider for SQL Server and pointing it to the same connection st

XML Source (Data is not loading to Destination ,Package Runs sucessful)

Hi First I run execute SQL Task SELECT TOP(10) [Message],opportunity_Id  FROM SBP FOR XML PATH ('GS_Opportunity'), ROOT('GSOpportunities') That will make 10 different file into single file then i have assigned this output to variable and it will output as XML Now I put Data flow task ,in it I put XML task and in source i have put XML Data From Variable and I select XSD file location,now XML task is showing me different source table and i have selected appropriate destination table but when i run this package it will not transform data from source to destination,Can you suggest me what is the problem i try to put data viewer it is not transforming any data  while insted of if i select direct file location instead of variable it is running good    

Error while executing a package through SQL Server Agent

Hi All,   I have a ssis package. It has 3 tasks, first task updates a record in Oracle database to set an Indicator to “Y”, second task process SSAS Cube and the third again updates the same record and sets the indicator to “N”. This package has 2 data sources, one is Oracle and the other is Analysis Services. I gave the credentials for the Oracle and use NT Authority for Analysis services.   When I am executing from BIDS package is executing successfully. But when I am calling the job to execute this package its throwing me the error. Below is the error.     Message Executed as user: User\Username. ...ion 9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:57:46 AM  Error: 2010-08-27 10:57:46.79     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2010-08-27 10:57:47.16     Code: 0xC0202009     Source: Ord_test Connection manager &q

SQL Server 2008 installation fails There was an error generating the XML document. Error code 0x84B

My system config: window XP - SP3 4 GB RAM, I have VS2008 with SP1, VS2010 Ultimate installed First when I tried to install SQL 2008 R2 Dev edition I got an error saying vs2008 SP1 was not installed, infact I had the SP1 installed in my m/c.  I did reinstalled the SP1 and tried to install SQL again. while in the final installation process I got the error  "Setup has encountered the following error: There was an error generating the XML document. Error code 0x84B10001." I uninstall the SQL server again to remove the files which were created from Unsuccessful installation I removed the register entry as suggested by this forum. reinstalled but no chance, I have tried all the option which I can get know in web. Please help me. here is the log files details  Exception type: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInfrastructureException 2010-08-26 14:54:53 Slp:     Message: 2010-08-26 14:54:53 Slp:         There was an error generating the XML document. 2010-08-26 14:54:53 Slp:     Stack: 2010-08-26 14:54:53 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.DataStoreService.SerializeObject(String rootPath, Object objectToSerialize, Boolean saveToCache) 2010-08-26 14:54:53 Slp:   &nb

SQL Server 2008 R2 fails after Windows XP Pro SP3 install

We recently encountered an issue with SQL Server 2008 R2 failing to run after the upgrade from Windows XP Pro SP2 to SP3. This was tested and reproduced (with varying results) on more than one computer. Steps to reproduce this. On a Windows XP Pro machine with Service Pack 2 installed, install SQL Server 2008 R2. Next upgrade Windows to Service Pack 3. Now upon opening SSMS or trying to start the SQL server service you receive the following message: "This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem". In some instances reinstalling/repairing SQL Server 2008 R2 does fix the issue.  In other cases the installer crashes and you need to use msizap to clean it up before you can reinstall.  In other cases you do not receive any errors at all. After testing in multiple virtual machines it looks like the cleaner the Windows install the more likely you are to run into this issue.  On machines with most or all windows updates (prior to SP3) installed you do not necessarily even get the failure.  On machines with just Windows XP Pro SP2 installed with no additional updates you get the failure every time. Obviously the SQL Server 2008 R2 prerequisites are installed in all cases in order to be able to install SQL server to begin with. Any help with this would be appre

SQL Server Express 2008 Setup fails. Support Rule "Restart Computer" Failed

Hi, I have been trying to install SQL Server Express for most of the day now without any luck.  Whatever I do setup fails at the stage of Setup Support Rules, Rule "Restart Computer" - Failed.  Needles to say I have restarted (several times!) and I have tried downloading  and installing the "Advanced", "Tools" and "Basic" versions, all give the same error.  Trying a repair does the same.  I have been searching the net for an answer without success, any help would be MUCH appreciated. Further info: I am trying to install a stand-alone installation on to a single PC running under XP SP 3 with .NET 3.5 SP1 installed.  The PC did have some earler versions of SQL server installed but I (?hopefully?) removed all of these through Control Panel.  Before trying the first SQL install I installed Windows installer 4.5 and PowerShell 1.0 as per the Microsoft Instructions. Running SQL Server Configuration Manager results in an error (MMC could not create the snap-in. The snap in might not have been installed correctly).  A directory structure for SQL server exists in "Program Files".  Services MSC shows SQL Server (SQLEXPRESS) as Automatic Statup (not running).  Trying to start this manually gives error 14001: "application configuration incorrect - re-install to fix" .......... Which is precisely what I'm trying to do.........:(

Connection to SQL server 2008 fails randomly

I'm sure a lot of you have seen this error: "A network-related or instance-specific error occurred while establishing a connection to SQL Server" Normally I would assume that this would mean that there's a problem connecting to the SQL server, but currently I'm having a hard time figuring out what the problem is and how to fix it The problem only occours randomly, but mostly it is when I from the same computer calls the server several times for example like this: For i as integer = 0 to 500 OpenConnectionToSqlServer(Bla bla) ReadData() CloseConnection() next I always makes sure to close the connection again. The more often I connect to SQL server in a row, the higher is the percentage of errors I have created my code so that if the above error happens, the program will wait for a second, then try again (after 3rd try it will stop trying)   I have several times been monitoring the SQL server at the same time, but I can't seem to see anything from there :(

Force SQL job to succeed even after a step fails in SQL server 2008

All, We are trying to force some jobs to succed even though one step in the job fails randomly. Now,we choose the property of the job succeed on failure, it still shows up as warning. How do we ignore the warning thrown by the job SQL job?? Any suggestions??   Thanks Kay

SQL Server 2008 Agent Fails to start in a Win 2008 Cluster

When I try to bring SQLAGENT Online, I get the following errors: EventID:53 [sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service.  CurrentState: 1 [sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435) [sqagtres] OnlineThread: Error 435 bringing resource online.   I just did a fresh SQL Cluster install as well.  When I installed the cluster, the service account was good to go.  I cant figure out what is going on.  SQL Server Engine starts fine.  Any ideas?

How to figure out if package runs in debug mode

Hi, I would like to know whether there is a possiblity to dynamically determine at runtime if a script is runing in debug mode or is being executed via SQL Server Agent? My goal is to prompt the user with some information e.g. initial catalog ensuring the package is being ran in the correct environment (development/production). However, as SQL Server Agent does not support MessageBoxes I would like to write some alternative code in this case. Thanks in advance!

Execute SSIS PAckage on Remote Server

I've seen several posts pertaining to this topic, but none of them covers what I'm having an issue with. I'm trying to create a "master job server". Essentially, I want to control all maintenance jobs from one central location. I've created SSIS packages that handle full backups, transaction log backups, index rebuilding, etc. on all of my production boxes. I've stored those packages in SQL Server. What I'd like to do now is create a separate job for each of those tasks on one central SQL Server and run those packages from there. I've been successful in creating a test job. The only step in the job is an SSIS PAckage execution step. The step successfully found the package I want to execute on the target server. When the job ran, I got the follwoing error: The job failed.  Unable to determine if the owner (ADMINSYS\arobinsf) of job SFO2PQE03 - Database Backup (FULL) has server access (reason: Could not obtain information about Windows NT group/user 'ADMINSYS\arobinsf', error code 0x2751. [SQLSTATE 42000] (Error 15404)). I want to make sure this job is executing on the remote server and not trying to execute locally. Is there something I'm missing in the set up of this job? I should also mention that the SSIS packages were created in SQL2K5, but I'm executing them from SSMS for SQL2K8. Any help would be appreciated! Thanks! A. M. Robinson

SSIS Package working on SQL Client Machine but when trying to run on SQL Server machine giving error

Hi We are trying to run SSIS package on sql server machine but sometimes it is giving error for data flow task containing Script Component. Error is - [SSIS.Pipeline] Error: component "SCR DimensionRelation" (1) failed the post-execute phase and returned error code 0x80004002. This error is not coming continuously. And also not for specific DFT. It comes for different DFTs as we rerun the package. Can anybody help me on this? We are using SQL server 2008. We are not getting any error on client machine :(:(

Where is System.Web.Extensions.dll now? Fails on remote hosted server.

I've read all the notes about how from RC2 to ajax.net the name space has changed. "The core ASP.NET AJAX assembly has been changed from Microsoft.Web.Extensions.dll to System.Web.Extensions.dll. In addition, the namespace has changed from Microsoft.Web.* to System.Web.*. For some reason, my app runs on my local machine but when I copy it to a remote server (that does not have the ajax updated installed) it fails.  How can I get an IIS installation to work?  Am I missing a dll that I can push up to the remote server?  I can't run the install on the remote machine becasue it's a hosted server. Thanks

SharePoint 2010 AD FS 2.0 Integration - login works then fails, looping back to the adfs server

Hello, I setup SharePoint 2010 Claims auth with AD FS 2.0 following this post: http://blogs.technet.com/b/speschka/archive/2010/07/30/configuring-sharepoint-2010-and-adfs-v2-end-to-end.aspx.  I have no access to the ADFS server, but I think they followed the steps as outlined.  If I do an iisreset (or just recycle the app pool of the SharePoint site) I can login successfully using the remote credentials.  Also I can under security in SharePoint search for and find users and roles from the remote ADFS server. If I close my browser, open a new browser and go to the site I am prompted to login again (this is fine).  I do, using the same credentials, and the login fails because I am redirected between the SP and ADFS servers until the ADFS server stops the redirecting.  I have been doing some digging and I found this post: http://blogs.technet.com/b/speschka/archive/2010/08/09/setting-the-login-token-expiration-correctly-for-sharepoint-2010-saml-claims-users.aspx, which describes the situation I have in point # 3 at the bottom.  I have tried setting the token time out as low as 1 second but this hasn't helped. I have also, just to see, tried setting all the other lifetime settings to 1 second - but no luck.  Because the initial login works I think the setup is correct, but something is maintaining some state that is causing the loop
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend