SQL 2008 SSIS packge never stops excecuting

Posted on 2009-04-08
Last Modified: 2013-11-10
I need to import data from a Oracle DB into my SQL 2008 server.
I've created an SSIS package which does this by means of an ODBC connection. Upon creating this package the excecution runs fine and the data is imported.

Now I need this import to be sheduled at 00:00 every day. I've done this with the SQL Server Agent by means of creating a task.

The tasks first drops the 2 tables, by means of a query.
Then the next tasks excecutes the SSIS package which first creates the tables and then copies the data.

However, the SSIS package keeps excecuting. No error is thrown, nothing happends. The log doesn't even show the 2nd tasks (SSIS package running) starting. However it is started because the 2 tables are allready created. Only the data does not get transfered.

I've tried multiple things so far:
-Saving the SSIS package on the SQL server or on the HD (C:\mypackage.dtsx).
-Tried different types of encryption (server handled, none, own password).
-Tried filling the connection string WITH and Without package password in the command window of the SSIS import step.

When running the package with the command /validate the job finished with no errors. However because /validate  is used no data is copied.

When I run the package from "Excecute Package Utility" the package runs fine and the data is imported. No errors are shown in the log.

When I run the package from a batch file with this line:
dtexec  "/FILE "C:\mypackage.dtsx" /DECRYPT mypassword /CHECKPOINTING OFF  /REPORTING EW "
It also runs fine.

When I copy this command in the SQL server Agent JOB  the job also keeps running (never ending).

The sqlserveragent has all the privedgles requirent to acces the local database. And the SSIS package has the password of the oracle database inside it. (It works when I run the batch so..)

How is it possible I can NOT run this package with the SQL server agent?

My workaround is running the batch file with windows tasks sheduler. However because now my database password is accessible due to the fact that the batch file contains the SSIS password (which then contains the database password) I have kind of an security leek.

Does anyone know how to solve this?
Question by:janwillem2
  • 4
  • 2
  • 2
LVL 22

Expert Comment

ID: 24098868
Do you have another machine to test the package?
Do you have a 32bit or 64bits? The package is correctling calling the ODBC 64bits exe or is calling ODBC 32bits exe?!

Author Comment

ID: 24099612
I only have one Windows 2008 server with SQL 2008. I do have other 2003 servers with sql 2005 to test.

its 32 bits.

And I suppose it's correctly calling the exe, how would the batch file be able to run it elseway?
I have no idea how to check this information tho.
LVL 22

Expert Comment

ID: 24099692
32bits in Windows Server machine?
... there are some bugs in 64bits, on previous version SQL 2005.
Maybe is a bug with Windows 2008 and SQL 2008, no?!
LVL 30

Accepted Solution

nmcdermaid earned 500 total points
ID: 24103997
It sounds like somerthing is 'prompting' (and holding you up). Quite possibly related to the password (even though it should work with password encryption)
I suggest you firstly add a task before all the data flows that does something like creates a file. Maybe an execute process task that has this:
That will write the time to a text file and you can check that it at least performs that step before starting. That will narrow it down to your data flows.
Also turn on logging and check the logs.
Have you tried logging on as the SQL Agent user and running it interactively? What user is the windows scheduler running as?
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.


Author Comment

ID: 24104632
With the windows Sheduler it rungs as it should (under administrative account).

Before the data is transfered the tabels are created. This happends in the same package, and works. But the datatransfer does not. I even manually added the same cmd string as shown above in the SQL agent (so with the password) and even then it simply doesn't go further then creating the tables.

I will try the time test and see if it helps. How can I enable some sort of advanced loggin option? Now I only see loggin of the step, but since it does not fail nothing is logged.

I will also try running it from the SQL user interactively and see what happends (so without a task.step).


Author Comment

ID: 24104972
I finally got it working. Used a flat file with password encryption and put the password in the command from the sql agent. Now the job completes succesfull. Wierd that it does not store the password when it's cleary asking for it at configuring the step.

Author Closing Comment

ID: 31568064
Thanks for the tips.
LVL 30

Expert Comment

ID: 24115018
Probably something to do with the interaction between SSIS and the Oracle driver.

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now