Solved

SQL 2008 SSIS packge never stops excecuting

Posted on 2009-04-08
8
537 Views
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?
0
Comment
Question by:janwillem2
[X]
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
8 Comments
 
LVL 22

Expert Comment

by:PedroCGD
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?!
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:janwillem2
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.
0
 
LVL 22

Expert Comment

by:PedroCGD
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?!
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 30

Accepted Solution

by:
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:
TIME /T > C:\TEST.TXT
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?
0
 

Author Comment

by:janwillem2
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).

0
 

Author Comment

by:janwillem2
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.
0
 

Author Closing Comment

by:janwillem2
ID: 31568064
Thanks for the tips.
0
 
LVL 30

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

737 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