Execute SQL Task in SSIS

Now you want to create a table in SQL Server Database. This can be easily accomplished using SSIS's Execute SQL Task control from SSIS Toolbox.

Assume that your table syntax is as follows:

IF  NOT EXISTS 
          (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMarks]') 
          AND type in (N'U'))
CREATE TABLE tblMarks
  (
     StudentId int, 
     Marks int
   )

1. Create an Integration Services Project in SQL Server Data Tools. Select the package and place an "Execute SQL Task" control on "Control Flow" tab.

2. Double click on the task and reach the editor. In the connection type, select "OLE DB" and select "New Connection"

3. In connection manager wizard select "New".

4. Enter the SQL Server's name and select the existing database to decide a destiny for the newly created table and finally test your connection. Click OK and reach back to Task Editor page.

5. Browse the button near "Sql Statement" and copy and paste the above query in the "Enter SQL Query" dialog box.

6. Click OK and reach back to designer page and check your db for table existence.

7. Execute your task and finally check to see that the table has been created in your DB through SSIS's "Execute SQL Task" process.

Cheers,

Venkatesan Prabu .J

Managing Director – KaaShiv Info Tech / MSP-MVP Mentor

www.kaashivinfotech.com