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

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

SSIS Job does not finish continously running

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server
I have a package that imports data from Oracle server into SQL Server. I use XML config file for SSIS job schedule and it runs fine on the Test box now i deployed the package on Prod server as a file system and schedule it as a Job but when i run the job it continously running and never get finish the job or stop. I manually stop the package and cannot find any error and could not find any clue . If i run the package on prod through SSIS manually it works fine. Any suggestion would be appreciated.

View Complete Post

More Related Resource Links

Issues with SSIS and SQL 2008 - Integration services not running

Hey guys, I installed SQL 2008 with complete BI studio including Integration Services. When i check in my services - i cant see my integraiton services in there When i try to connect to Integration Service using localhost, I cant connect it And when i try to run setup of SQL 2008 - It shows that Integration Services already installed and that what i checked when i fetched my Installation tool report from SQL Server to verify i have it installed. Why can i access Integration services, if i already have it, Please suggest with ideas, checks i can make to ensure every setting is in place. Thanks. Would appreciate for quick response.  

SSIS package Scheduled Job Not Running

I'm not sure which way to research this issue.. I have an SSIS package that we can run just fine manually. But, when we put it into a scheduled job, it won't run. The package is on the same system as the SQL Server (2005), but it does call for updates from tables on another SQL Server (2000) on another domain (trusted). So, I'm thinking this might be a permissions / security issue, but not sure where to start... The package ProtectionLevel is "don't save sensitive", but that didn't help. Any help would be appreciated. Thanks. jill

Problems running a bat file within a SSIS package

I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!   Thanks in advance.

Issues with running SSIS packages programmatically

Hi, I have spent days trying to solve this problem and still stuck with this and I have posted some questions already in forums, but didn't get satisfactory answers. I am trying to be more clear this time and hope to get a better answer. I have gone through this article already http://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx and here are my issues (I need to run the SSIS package from ASP.NET) option 1 is not suitable for me, because it may recycle worker process if it consumes memory option 2 is also not suitable because of security issues in creating a new process and passing the context to new process looks very complicated for me (according to the support article) option 3 is not suitable because using SQL Server Agent to run SSIS package is not allowed by the company I am working for(I guesss it requires installation of db engine on application server, not sure). but SSIS is installed on the application server. option 4&5 will have the same issues as options 1&2. I guess the only option left now is to create a windows service and start the service from ASP.NET. but will this allow running multiple packages in parallel? OR is there a better alternate solution for this? please let me know. Thanks.

SSIS package is giving error when ran thru JOB...its running good when I execute in BI Studio or Exe

Friends I am getting weird issue...I built an SSIS package. In one variable I used express builder and built like this... RTRIM( (DT_STR, 2,1252)  (((DT_I4) ( (DT_WSTR, 3) (SUBSTRING( @[system::machineName] , 12, 16 ))) +1) /2)) when I run the package outside ie., with VStudio or execute utility it running absolutely with out any problem. But when I created a job just to execute that package in particular timings...the job is totally failing by throwing error....the error output is like the following: Started:  10:22:33 AM Error: 2010-02-27 10:23:06.23    Code: 0xC00470C2    Source: Rerun_MissingFiles    Description: Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_I4. Error: 2010-02-27 10:23:06.23    Code: 0xC00470C4    Source: Rerun_MissingFiles    Description: Casting expression "(SUBSTRING( @[system::machineName] , 12, 16 )))" from data type "DT_WSTR" to data type "DT_I4" failed with error code 0xC00470C2. End Error Can any one help me please? Thanks in advance.

Problem running SSIS package in Jobs

Hello friendsI'm trying to run my SSIS packages as a step inside a sql agent job.This runs normally when the package uses the SAME database as used to create my job.The problem occurs if I create a job to run the package and this package must execute queries in another database. Probably authentication problem. So how to authenticate my package and the job runs normally ?thanks in advance.

Can a stored procedure programmatically determine if an SSIS package is running?



If I have the 'ExecutionInstanceGUID' value for an SSIS package that I know was started, is there a way that a stored procedure can programmatically determine whether the SSIS package still is running?

Thanks -



SQL Agent - Running SSIS Package - Windows Server 2008/Windows 7 - Messenger Services


We have tried to run a SQL job for SSIS Package. But it failed during the execution with the error "[364] The Messenger service has not been started - NetSend notifications will not be sent". But in the job, no notifications have been configured and in windows server 2008/Windows-7 there is no messenger service at all.

When we search for the the messenger service in windows 7/windows server 2008, we found that service is not in both the OS and it was replaced by msg.exe. 

Any help or workaround would be appreciated.

Thanks in advance.

PS: The normal SQL jobs other than SSIS are working fine without any error.

Vijay Pandurangan

How do I troubleshoot SSIS package hanged for running?


[This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]

My SSIS package is running in a SQL Server Agent job, but when it is running, it hangs. I could not see error messages from the job history. What is the problem?
There might be various reasons to cause a SSIS package hanged for running and generally for a hang issue, there is no log information helpful for diagnostics. These are the difficulties of troubleshooting hang issues. In this FAQ, we would like to give you three common aspects to help you perform self-troubleshooting:
1. Resource bottleneck
2. Blocking or Deadlock
3. Poor performance query

Resource Bottleneck
Generally resource bottleneck includes bottlenecks of memory, processor, physical disk or network interface. You can run Performance Monitor (perfmon.exe) to collect the trace with the following counters:
Processor: %Processor Time
Processor: %DPC Time
Processor: %Privileged Time
Processor: Interrupts/sec
Memory: Available Bytes
Memory: Pages/sec
Memory: Page Faults/sec
Memory: Committed Bytes

Running SSIS job step as Proxy account does not find log provider for windows event log

When I run a ssis job step using a proxy account i get this error message:

Description: The log provider type "DTS.LogProviderEventLog.2" specified for log provider "SSIS log provider for Windows Event Log" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name.  End Error  Error: 2010-02-02 14:43:03.88     Code: 0xC0010021     Source:       Description: Element "{028F8760-7E28-4F62-A204-66F33F4064BE}" does not exist in collection "LogProviders". 

If the account is a member of administrators group on the sql server the job step works fine.

Since the logprovider DTS.LogProviderEventLog.2 exists on the system I guess that this has something to do with access rights, probably to some registry key(s), but I cannot find out which part of the registry the proxy user does not have access to.

When running the same package using DTEXEC in a Command Prompt and using RUNAS <proxyuser> the package works fine and writes to the event log.

Kind regards


update 17-Feb-2010: Microsoft has reproduced this situation and I hope to get a workaround or fix soon :-

Running SSIS package from Stored Procedure using dtexec and Pass boolean value



We are using SSIS 2005 and sql server 2005. My package has a boolean type package level variable. Eg: IsClientNull boolean type.

I call this package from stored procedure. In addition to passing values for other parameters, how can I pass boolean value.

My code is like this:

SET @CMD='dtexec ' +
'/FILE ' + @pShareName + @pPackageName + ' ' +
'/MAXCONCURRENT " -1 " ' +
'/SET "\Package.Variables[User::StartDate].Properties[Value]";' +
CONVERT(char(10), @pStartDate, 120) + ' ' +
'/SET "\Package.Variables[User::EndDate].Properties[Value]";' +
CONVERT(char(10), @pEndDate, 120)
IF (@pClient IS NULL)

 SET @CMD = @CMD + ' ' +
 '/SET "\Package.Variables[User::IsClientNull].Properties[Value]";' +

2 SSIS services running on one machine



I have developed a set of packages which i am attempting to deploy to a server which i did not configure.

The Server has three versions of SQL installed - SQL 2000 (default), 2005 and 2008.

When i run my deployment manifest i receive an error: 0xC0011008 - I think this is because my package is developed in BIDS 2008 and the server is running SSIS 2005.

This is my issue....when I look in the services on the server - i have two integration services, both running (i didn't think this was possible?)

1. SQL Server Integration Services
2. SQL Server Integration Services 10.0

When i connect to Integration Services through SSMS - the version displayed is 9.0.4035

There is also no DTS directory under the C:\Program Files\Microsoft SQL Server\90\ or C:\Program Files\Microsoft SQL Server\100 directory ....?

How can i rectify this? I want to end up with SSIS 2008 installed.

Thanks, Clay


Passing parameter when running SSIS



I have  question and not sure ssis can do that

I have one store procedure that has one date as a parameter like 

exec Employeehiredate '10/10/1998'

now i want in "Execute Sql task"  such that

 when i run this package from server  after deployment  i can set date value and it will run package by taking that value.

Please let me know if this is achievable  or not  or how to achieve this.

Running SSIS on 64-bit server - dll issue



We have a SSIS package which refreshes an Excel 2007 file using a script task - references the Microsoft.Office.Interop.Excel.dll

Runs Ok on 32-bit dev PC - returns this error on server: "Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154.

at ST_351c98ccd83b4e988e50f7e36003277f.vbproj.ScriptMain.RefreshExcel()

at ST_351c98ccd83b4e988e50f7e36003277f.vbproj.ScriptMain.Main()

--- End of inner exception stack trace ---

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, Cul

Error Running SSIS Package as SQL Agent Job loading a file from network drive, works fine in bids an

Having problems reading an excel file from a networked drive while running a package as a SQL Agent job step as a type "SQL Server Integration Services Package". I have agent running as an AD account, and I can use the execute package utility logged in as this account and it works. When I log into the server as this account, I can see the network drive, read/write/create to folders on the drive but when I try to run the agent job it cant find the file. I tried using the unc path in the config file rather than a mapped drive, still no luck. If I use a local drive (c) in the config file, the job works fine so it has to do with the network drive. Running windows server 2003 standard R2, SQL Server 2005 standard sp3. Any ideas out there?

running ssis package automatically when source updated


hi this is babu.

I have one doubt on running ssis package. I have a dataflow task for inserting data from oledb source to oledb destination.

i have to run this dataflow task when the source is updated(like inserting records and updating records).

if there is no updations of source  i dont want to run.

what can i do... plz calrify my doubt

SSIS Package not running but validating OK

We made some changes to our SSIS package to support UTF-8 incoming files and now the package stops at the validation step and never executes the first task.  Hence the return status Success even though nothing has been imported to the database.  Any ideas?
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