export the output from a stored procedure to flat file using (SSIS), and schedule the job on a particular day say monday?
Hi Guru's,
Need simple steps to export the output from a stored procedure to text file using SQL Server Intelligence Services(SSIS), to run the report weekly say every monday
May 16th, 2012 1:29pm
Create an SSIS package with a dataflow that uses a stored procedure for the source and a flatfile destination. Schedule the package using SQL AgentChuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 1:38pm
Thnak you..when i did that in the DFT (OLEDB source), it gave an error saying the component has no available input columns
May 16th, 2012 1:43pm
Does your stored procedure use dynamic SQL to return the records?Chuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 1:45pm
Yes..it does
May 16th, 2012 1:54pm
Then that is the problem - dynamic sql should be avoided as much as possible in stored procs. There is a hack to make it work if your proc returns a consistant set of columns - if it doesn't then it would never work in SSIS anyways.
At the top of the procedure if you put code that looks like this:
IF 1=2
SELECT Cast(0 as int) as fieldname1, cast('st' as varchar(50) as fieldname2 ....etc
Where you explicitly define a single row that has the same datatypes and names as your expected results. Regardless of the fact that 1 never = 2 it will use that as the column list.Chuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 2:10pm
but i didnt write the stored proc..I just need to ecex the proc and load to text file
May 16th, 2012 2:56pm
Doesn't matter if you wrote it or not - as it currently is written it will not work as a data source in an ssis package.Chuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 3:03pm
Thanks alot. and how do I schedule the job to be able to run every monday mornings 8 am?
May 16th, 2012 3:07pm
Create a SQL Agent job to run the package and schedule the job for monday mornings at 8AMChuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 3:08pm
How can i do that..can u send me screen shot guide.or step by step proc..thanx in advaance
May 16th, 2012 3:27pm
Talk to your DBA. If you don't know how to do it then you probabally don't have access and if you do then you shouldn't.Chuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 3:28pm
Just my 2C, this is how one would create and schedule an Agent job in SQL Server: http://www.codeproject.com/Articles/14401/How-to-Schedule-and-Run-a-SSIS-package-DTS-Job-in (a mini tutorial).
Be aware that the default protection level of the package would not cut in, you 'll need to change it to RelyOnServerStorage.
PS: If I understood correctly, the Stored Procedure uses sp_executesql, is that right?Arthur My Blog
May 16th, 2012 3:29pm
How to Schedule and Run a SSIS package ( DTS ) Job
In the SQL Server 2005, after you create a SSIS package ( DTS), you want to create a job and schedule to run it. You will get the error to prevent you to run the job. What is the problem?
Here is why: SQL Server 2005 is quite different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, you need to go through the security
layer in order to run the job.
The logic is like this:
The following steps can be followed to get the job done.
The work environment is MS SQL Server Management Studio and you log in as sa.
I. Create job executor account
Highlight Security->New Login, say to make login as devlogin, type your password, default database can be your target database.
Server roles: check sysadmin
User mapping: your target database
Msdb database: you make sure to include
SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole
Then click OK
II. Create SQL proxy account and associate proxy account with job executor account
Here is the code and run it the query window.
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
III. Create SSIS package
In MS SQL Server Business Intelligence Development Studio, you use job executor account devlogin to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business
Intelligence Development Studio. Compile/build this package.
IV. Create the job, schedule the job and run the job
In SQL Server Management Studio, highlight SQL Server Agent -> Start. Highlight Job ->New Job, name it , myJob.
Under Steps, New Step, name it, Step1,
Type: SQL Server Integration Service Package
Run as: myProxy
Package source: File System
Browse to select your package file xxx.dtsx
Click Ok
Schedule your job and enable it
Now you can run your job.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 4:59pm
I cannot open the article..it says error
May 16th, 2012 4:59pm
How to Schedule and Run a SSIS package ( DTS ) Job
In the SQL Server 2005, after you create a SSIS package ( DTS), you want to create a job and schedule to run it. You will get the error to prevent you to run the job. What is the problem?
Here is why: SQL Server 2005 is quite different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, you need to go through the security
layer in order to run the job.
The logic is like this:
The following steps can be followed to get the job done.
The work environment is MS SQL Server Management Studio and you log in as sa.
I. Create job executor account
Highlight Security->New Login, say to make login as devlogin, type your password, default database can be your target database.
Server roles: check sysadmin
User mapping: your target database
Msdb database: you make sure to include
SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole
Then click OK
II. Create SQL proxy account and associate proxy account with job executor account
Here is the code and run it the query window.
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
III. Create SSIS package
In MS SQL Server Business Intelligence Development Studio, you use job executor account devlogin to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business
Intelligence Development Studio. Compile/build this package.
IV. Create the job, schedule the job and run the job
In SQL Server Management Studio, highlight SQL Server Agent -> Start. Highlight Job ->New Job, name it , myJob.
Under Steps, New Step, name it, Step1,
Type: SQL Server Integration Service Package
Run as: myProxy
Package source: File System
Browse to select your package file xxx.dtsx
Click Ok
Schedule your job and enable it
Now you can run your job.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 5:02pm