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)
 SSIS: Run a script & generate CSV as output

Author  Topic 

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 12:50:33
Hi All,

I have a SQL script. It is a SELECT query that reterive data as 3 field(columns). In a SSIS package, I want to run this script, and want to generate a CSV file as a output. I also want to schedule this package to run weekly on a certain day/time.

What is the easiest way to do this in SSIS. Can someone put some steps (in order). Questions are;

1) In SSIS what should I use under Control Flow tab?
2) In SSIS where I am suppose to provide the database connection?
2) In SSIS which Data flow sourcse should I use? And where should I put the script?
3) In SSIS which Data flow destination (where I write the CSV file) should I use?
4) How Can I schedule this SSIS package?

Thanks a million for your help.

Zee




sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 12:54:19
Use Data flow task . Oledb source and flatfile destination in SSIS and use SQL Server Agent Jobs to schedule the package.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 13:34:29
Thanks so deep. That is exactly what I did before reading any reply.
But how to I schedule this package using "SQL Server Agent Jobs". Please list some steps. Thanks a million in advance.

Zee
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 13:40:01
Make job step -type - integration services and choose packages, schedule it .
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:02:57

It may sound strange, but from where can I open "SQL Server Agent Jobs" ?
Please suggest.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:06:20
I am looking in online help. This is what I read.


To create a job
In the Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

Expand SQL Server Agent.

Right-click Jobs, and then click New Job.

On the General page, in the Name box, type a name for the job.

Clear the Enabled check box if you do not want the job to be run immediately following its creation. For example, if you want to test a job before it is scheduled to run, disable the job.

In the Description box, enter a description of what the job does. The maximum number of characters is 512.


Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 14:25:46
You need to have permission to create jobs and see SQL server Agent.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:45:08
I do have permission. But I am having problems...please look at this screenshot.

http://www.flickr.com/photos/7714646@N07/3082224009/sizes/o/

Under SQL Managment Studio I followed the above step to open the "New Job" screen; in the "general" page I provide the "name" & "description" of the job and kept the "category" to default (that is local); than in the "steps" page I clicked "new" which opened the "new job step". Than I provide the "step name", and under "type" I selected "SQL Server Integration Services Package", and kep the default under "Run as" as "SQl Agent Service Account"; than I selected the appropiate "Server name". Under the "Package" text box when I try to browse and select the package, I dont see a place where I can broswe the .dtsx file. Under the "Package" text box if I manually put the location, it gives an erorr "Cannot find folder". PLEASE SEE THE ABOVE SCREENSHOT.

Help please....



http://www.flickr.com/photos/7714646@N07/3082224009/sizes/o/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 14:47:02
How did you save package once you are done? in Filesystem or MSDB?
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:47:05
Here is the error:

http://www.flickr.com/photos/7714646@N07/3083089312/sizes/o/
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:48:12
I saved it as dtsx extension. Am I saving it wrongly? What should be the file extension?

Please suggesr

Zee
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 14:48:53
Can you import package to MSDB and use it?
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:51:03
This is how my package loaction looks like.

http://www.flickr.com/photos/7714646@N07/3082259765/sizes/o/
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:52:17
"Can you import package to MSDB and use it?". Please explain further. How can I do this.

Thanks for all your help.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 14:53:32
Is it in Local machine or Server?

If Local machine:
Use like this:
\\Host-name\E$\......
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:57:33
The package exist on the same server where I am trying to schedule it. I tried the following, but got the same error "cannot find folder".

\\TSG1\E$\Projects\.......\Package.dtsx
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 14:58:01
*where TSG1 is the server name
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 15:02:43
Then choose local drive.

You can migrate to MSDB in Object explorer-Connect to Integration Services - MSDB.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 15:15:17
I tried local, and it did not work either.

In SQL Managment Studio I cannot connect to Integration Services.
See this screenshot..

http://www.flickr.com/photos/7714646@N07/3083146546/sizes/o/

Where TSG1 is my server name....I tried putting "local" but that also did not work.

Please help.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-04 : 15:46:13
sodeep & others please help....
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 15:57:05
quote:
Originally posted by zeeshan13

The package exist on the same server where I am trying to schedule it. I tried the following, but got the same error "cannot find folder".

\\TSG1 Put your local machine host name\E$\Projects\.......\Package.dtsx

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -