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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics