how to execute batch of scripts by one DTS or SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » how to execute batch of scripts by one DTS or...

how to execute batch of scripts by one DTS or SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

himanshuk

Posted Wednesday, October 10, 2012 4:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:14 AM
Points: 3, Visits: 18

I need to know how to execute batch of scripts stored in some folder one by one through DTS or SSIS or is there is one script which execute all bunch from folder and if error occours then STOP at that POINT and SHOW ERROR WITH THAT PERTICULAR SCRIPT NAME

Post #1370816

John Mitchell-245523

Posted Wednesday, October 10, 2012 4:27 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

Use a Foreach Loop Container in SSIS. Your requirements are quite vague, so please try that and post again if there's anything in particular that you don't understand.

John

Post #1370823

Ravi Chhabria

Posted Wednesday, October 10, 2012 4:42 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, April 04, 2013 4:58 AM
Points: 52, Visits: 163

I assume you have a bunch of .sql files in a folder that you want to execute. What you can do is use a foreach loop container in SSIS and get the name of file in a variable.

Then inside foreach loop, place an execute sql task with SQLSourcetype as FileConnection. Configure the Connectionstring property of Connection manager of this Fileconnection to read value from variable being used in foreach loop container.

If any of the script fails, package will fail.

Hope this helps !!

Post #1370830

« Prev Topic | Next Topic »

Permissions