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?!
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Abstract Express Replacement Software 12 25
migration MS SQL database to Oracle 30 61
Count with a subquery showing details 10 43
string fuctions 4 26
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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