Automate DTSX without Integration Services

[mekohler]

Hi, I have a database on a server which doesn't have the full version of SQL, it doesn't have integration services installed. I have created a dtsx package, how can I get my IT person automate this package so that it runs each night without having the integration services? He doesn't seem to understand this process.
Thanks,
Michael Kohler

 

[RiverGuy]

You just need to run it on a server which has the SSIS service installed. The server the package runs on doesn't need to be either the source or destination for the data itself.

 

[mekohler]

Thanks, I tried this and I keep getting the error
"Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "GIS Import" (1002) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure."
Is this to do with the server with SSIS serice installed is 64bit machine and the connection is a 32 bit?
Thanks,
Michael Kohler

 

[mrdenny]

Yes, probably. You'll need to run your package via the 32bit version of DTExec which you'll find in the C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn folder.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[mekohler]

I found the answer to this problem. I'm using the OLE DB connection from IBM for their AS400. I installed the latest service pack from IBM and the connection works fine.

 

[RiverGuy]

If you run into performance problems with the driver, then you might try the following Microsoft/DB2 driver if you have the correct licensing for it. In the past, I have seen it used to provide better performance than IBM's driver on 64-bit systems.

http://msdn.microsoft.com/en-us/library/ms178027.aspx

 

[mekohler]

I just want to get something straight. If I am running a package that accesses data from a seperate SQL server, do I create a proxy on the server that I created the job on or do I create the proxy on the server on which the data resides? And as far as credentials, is it possible to create a credential on one server than create a proxy on a different server and access that credential?
Thanks,
Michael Kohler

 

[mrdenny]

You don't need a credential, unless you want the job step to run under a different domain user account.

You can't create a credential on one system, and create a proxy based off that credential on another system. The proxy would need to be created against a credential on the local system.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog