Create a job with a SSIS package inside

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Create a job with a SSIS package inside

Author

Topic

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-30 : 05:59:03

Hello,
I would like to have a scheduled job that exports an SQL table to an xls file. I did the following: First I created a package that exports the table to xls, I saved this SSIS package. (If I run this package manually its works fine)
As a next step I created a new job with following properties:
General
Name: export
Owner: is me
category: [Uncategorized (local)]
Steps
Stepname: exportfile
Type: SQL Server Integration Services Package
Run As: SQL Agent Service Account
I select the package source (SQL Server) I select the server and as a final step I selecht my package mentioned above. I press OK. As a final step I have to press OK once and then I get the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for JobStep 'export'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476

------------------------------
BUTTONS:

OK

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-30 : 07:19:13

Have you deployed the package properly?

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-30 : 07:54:33

which Version of SQL Server?

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-30 : 09:00:20

I'm running SQL server 2005 version 9.00.1406.00

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-30 : 09:10:52

Did you save it to MSDB while deploying?

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-30 : 09:22:29

I saved the package to SQL server, I already tried to save to the file system but that did not work either.

I'm still trying to find out if the SSIS package is deployed properly.

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-30 : 12:20:46

I read the article from link http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx and in the chapter "Managing SSIS Packages with SQL Server Management Studio" you can see Figure. On that figure you see the integrarion services with the packages. Now the problem is. I do not see the packages. I tried to connect to this service the following way.
I press: Connect
I press: integration services
Then al fields are grey except server name. Here I select browse for more and then nothing... no local servers to choose from. How is this possible?

Sorry for my english I'm not used the write in English

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-30 : 12:48:50

Run SQL Server Configuration Manager on the server (Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools). Do you have the SQL Server Integration Services service running?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-30 : 15:22:59

You have to install integration services first.

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-30 : 16:13:55

I've locked in on the server directly and opened sql server configuration manager. Down below we can see de services that are running. But SSIS is not running and not available. But if I manual import a txt or excel file it works fine. How is that possible if it is not even running? Next question, is it possible to install only SSIS from the CD? Or what is my next step?

Thank you for your help so far.

Name State Start Mode Log On As Process ID Service Type
SQL Server FullText Search (SHAREPOINT) Running Automatic LocalSystem 3120 Full Text
SQL Server (SHAREPOINT) Running Automatic LocalSystem 4964 SQL Server
SQL Server Agent (SHAREPOINT) Running Automatic LocalSystem 5768 SQL Agent
SQL Server Browser Running Automatic LocalSystem 2624 SQL Browser

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-30 : 22:16:02

Yes you can. Choose Integration Services only while installing

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-31 : 04:01:20

Thank you for your help. We will install it this weekend.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-31 : 05:47:15

Its possible because when you open a SSIS job, you are using VS2005 not SQL 2005 and as you are using VS2005s drivers (OLEDB driver to SQL Server), there is no need for the service to be running as there is no management of the package for SQL Server to worry about.

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-31 : 08:44:04

@RickD: I'm not sure I understand what you mean. I made a new package where i select "Microsoft OLE DB Provider for SQL Server" as the destination. I Saved the package and put it into the job. I still get the same error as mentioned before.

Do you also think is has to do with the installation "Integration services"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-31 : 09:53:15

Yes, that is what I am trying to say. If you open the package and run it manually, you are not using the Integration Services service. When you try to run it from a job, it will first look for the Service to be running as it needs this to decide how to interpret the package.

Riklinssen
Starting Member

20 Posts

Posted - 2008-10-31 : 10:38:15

I installed the integration service butt is seems to be unavailable still. Do I have activate it before is ready for use??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-31 : 11:59:42

what do you see in Config manager for services in SSIS?

Riklinssen
Starting Member

20 Posts

Posted - 2008-11-02 : 04:13:40

I still see the same as mentioned in reply number 8. The same list. I'm pretty sure it is installed so this is strange.

Riklinssen
Starting Member

20 Posts

Posted - 2008-11-13 : 04:55:37

I now know what the problem is. I work at a small company and we SQL workgroup and not SQL standaard. This means that integration services is not available...
Is there a way to import/export data from txt and/or xls files into or out of the SQL database within a Job without using packages.
Can I use an import querie like mentioned here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 in a scheduled job or do I need integration services for this to?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-11-13 : 05:21:40

You can use BCP or BULK INSERT to load to a staging table or to your actual table depending on whether you need to manipulate the data or not. You'll find the syntax in BOL (Books Online).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources