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. |
|
|
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 |
|
|
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 . |
|
|
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. |
|
|
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 jobIn 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. |
|
|
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. |
|
|
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/ |
|
|
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? |
|
|
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/ |
|
|
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 suggesrZee |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-04 : 14:48:53
|
Can you import package to MSDB and use it? |
|
|
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/ |
|
|
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. |
|
|
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$\...... |
|
|
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 |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-12-04 : 14:58:01
|
*where TSG1 is the server name |
|
|
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. |
|
|
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. |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-12-04 : 15:46:13
|
sodeep & others please help.... |
|
|
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
|
|
|
Previous Page&nsp;
Next Page
|