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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?!
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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?

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

696 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