Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Scheduled Job to run SSIS Package on SQL Server 2005

  
   Database Help (Home) -> DTS RSS
Next:  Stats comp.databases.mysql (last 7 days)  
Author Message
Van T. Dinh

External


Since: May 06, 2004
Posts: 205



(Msg. 1) Posted: Mon Dec 07, 2009 11:25 pm
Post subject: Scheduled Job to run SSIS Package on SQL Server 2005
Archived from groups: microsoft>public>sqlserver>dts (more info?)
Windows 2003 (Standard x64) SP2 named "W2003XXX". SSIS Server is therefore
named "W2003XXX".
SQL Server 2005 (Standard x64) Build 9.00.1399.06 named instance
"W2003XXX\SQL2005YYY". Waiting for I.T. Support Group to install SP3.

In SQL Server Business Intelligent Visual Studio, I created a fairly simple
SSIS Project/Package "CannedSpecs" deleting records from JET Tables and then
append records from SQL Server Tables to the now-empty JET Tables.

The SSIS package CannedSpecs.dtsx worked fine in Visual Studio after setting
the Project property "Run64Runtime" to False. If this Property is set to
True, I get errors:

Error: 0xC0202009 at CannedSpecs, Connection manager "Connection 2":
An OLE DB error has occurred. Error code: 0x80040154.

An OLE DB record is available. Source: "Microsoft OLE DB Service
Components"
Hresult: 0x80040154 Description: "Class not registered".


I then saved the SSIS package on both the SSIS Server (as File System SSIS
Package) and also as SQL Server SSIS Package.

If I use DTExecUI, all 3 options (File/SSIS Package Store/ SQL Server)
succeeded.

If I use DTExec, all 3 options (File/SSIS Package Store/ SQL Server) failed
with the following commands:

DTExec /FILE "Full-path to CannedSpecs.dtsx"
DTExec /DTS "\File System\CannedSpecs" /SERVER W2003XXX
DTEXEC /SQL CannedSpecs /SERVER W2003\SQL2005YYY

If I create a SQL Server Agent scheduled job using both types "SQL Server
Integration Package" and "Operating System (CmdExec)", each with the 3
options as above, everyone of the 6 possible combinations failed.

All failures described above have errors similar to posted errors above
indicating that they all were run as if Run64Runtime = True.

Questions:

1. Is there anyway I can specify Run64Runtime = False in using DTExec or in
SQL Server Agent Job step?

2. Even if the general solution is not possible, is there anyway I can
create a SQL Server Agent Scheduled Job that can run the SSIS package using
1 of the 3 modes above?

Many thanks for any advices/suggestions offered.

--
Van T. Dinh

 >> Stay informed about: Scheduled Job to run SSIS Package on SQL Server 2005 
Back to top
Login to vote
Van T. Dinh

External


Since: May 06, 2004
Posts: 205



(Msg. 2) Posted: Wed Dec 09, 2009 9:25 pm
Post subject: Re: Scheduled Job to run SSIS Package on SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
SQL Server has been updated to SP3 (Build 9.00.4035) but the results of the
repeated tests are exactly the same as previous post.

Any idea?

Thanks & cheers
--
Van T. Dinh



"Van T. Dinh" wrote in message

> Windows 2003 (Standard x64) SP2 named "W2003XXX". SSIS Server is therefore
> named "W2003XXX".
> SQL Server 2005 (Standard x64) Build 9.00.1399.06 named instance
> "W2003XXX\SQL2005YYY". Waiting for I.T. Support Group to install SP3.
>
> In SQL Server Business Intelligent Visual Studio, I created a fairly
> simple SSIS Project/Package "CannedSpecs" deleting records from JET Tables
> and then append records from SQL Server Tables to the now-empty JET
> Tables.
>
> The SSIS package CannedSpecs.dtsx worked fine in Visual Studio after
> setting the Project property "Run64Runtime" to False. If this Property is
> set to True, I get errors:
>
> Error: 0xC0202009 at CannedSpecs, Connection manager "Connection 2":
> An OLE DB error has occurred. Error code: 0x80040154.
>
> An OLE DB record is available. Source: "Microsoft OLE DB Service
> Components"
> Hresult: 0x80040154 Description: "Class not registered".
>
>
> I then saved the SSIS package on both the SSIS Server (as File System SSIS
> Package) and also as SQL Server SSIS Package.
>
> If I use DTExecUI, all 3 options (File/SSIS Package Store/ SQL Server)
> succeeded.
>
> If I use DTExec, all 3 options (File/SSIS Package Store/ SQL Server)
> failed with the following commands:
>
> DTExec /FILE "Full-path to CannedSpecs.dtsx"
> DTExec /DTS "\File System\CannedSpecs" /SERVER W2003XXX
> DTEXEC /SQL CannedSpecs /SERVER W2003\SQL2005YYY
>
> If I create a SQL Server Agent scheduled job using both types "SQL Server
> Integration Package" and "Operating System (CmdExec)", each with the 3
> options as above, everyone of the 6 possible combinations failed.
>
> All failures described above have errors similar to posted errors above
> indicating that they all were run as if Run64Runtime = True.
>
> Questions:
>
> 1. Is there anyway I can specify Run64Runtime = False in using DTExec or
> in SQL Server Agent Job step?
>
> 2. Even if the general solution is not possible, is there anyway I can
> create a SQL Server Agent Scheduled Job that can run the SSIS package
> using 1 of the 3 modes above?
>
> Many thanks for any advices/suggestions offered.
>
> --
> Van T. Dinh
>
>
>
>

 >> Stay informed about: Scheduled Job to run SSIS Package on SQL Server 2005 
Back to top
Login to vote
Van T. Dinh

External


Since: May 06, 2004
Posts: 205



(Msg. 3) Posted: Thu Dec 10, 2009 12:25 am
Post subject: Re: Scheduled Job to run SSIS Package on SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
For anyone who has similar problems, the solution is found in MSDN article:

Integration Services Considerations on 64-bit Computers
http://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx

--
Van T. Dinh



"Van T. Dinh" wrote in message

> SQL Server has been updated to SP3 (Build 9.00.4035) but the results of
> the repeated tests are exactly the same as previous post.
>
> Any idea?
>
> Thanks & cheers
> --
> Van T. Dinh
>
>
>
 >> Stay informed about: Scheduled Job to run SSIS Package on SQL Server 2005 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
NEWBIE - can't run SSIS package in SQL server agent schedu.. - I have a SSIS package that runs fine in Visual Studio. But if I put it in a scheduled job in SQL Server agent, it won't run. Both VS and SA use the same account to run. The error I get in the logs is an ODBC error. The package does have an ODBC..

migrated DTS to SSIS package, how to run SSIS package from.. - Hi all, I am kind of new in this topic and get stuck for awhile. I migrated DTS packages from SQL Server 2000 (Windows 2000) to be SSIS packages on SQL Server 2005 (Windows 2003 Server) but don't know how to modify ASP code to run SSIS on a local..

parameterize query in SSIS package - SQL 2005 - Hi All, I have created a query to run in a .dtsx package. I would like to parameterize it so that a user can input a value via the Execute Utility dialog when the .dtsx package is double-clicked. Any info would be appreciated! Thanks, Gloria

Scheduled DTS Package - I have a DTS package that will run fine in almost every place except when it's scheduled. I can right-click on the package and select "Execute" and it runs ok. I can open the package in design view and run it, and it's ok. I can open up a ...

SSIS Newbie: Create new text logs when a SSIS package is run - Hi there, I was wondering if you could point me in the right direction on how I could create new csv/ txt files whenever a SSIS package is run? As far as I could understand, the SSIS have the ability to create logs, but I have noticed that it creates..
   Database Help (Home) -> DTS All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]