Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL 2008 SSIS packge never stops excecuting

Posted on 2009-04-08
Medium Priority
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?!
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 30

Accepted Solution

nmcdermaid earned 1500 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

597 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