Execute SQL scripts from SSIS

• PSB

  SSChampion

  Points: 13195

  More actions

  August 24, 2010 at 8:10 am

  #239319

  Hi,

  I want to exceute a sql script using SSIS . An example of the sql script would be like below:

  EXEC create_core_counties_table 'BBB'

  GO

  EXEC create_facility_Services_Table

  GO

  drop table abcdl

  drop table efg

  select * into ABCContractsFinal from dbo.[ContractsFinal]

  select * into CVGZipRadCSBA_Report2 from dbo.[ZipRadCSBA_Report2]

  select * into HMOCMD_Contracts_ABC_Exceptions from dbo.CMD_Contracts_ABC_Exceptions

  select * into ABCNonConveredZips from dbo.NonConveredZips

  select * into ABCHSD23a_DETAIL_REPORT from HSD23a_DETAIL_REPORT

  The above stored procs inserts data into core counties table and Facility services table .

  Any help appreciated .

• Raunak Jhawar

  SSCoach

  Points: 15701

  More actions

  August 24, 2010 at 10:48 pm

  #1211447

  You have two options:

  1. Implement an EST(Execute SQL Task)

  2. Implement a Script Task with embedded .NET code

  Raunak J

• Koen Verbeeck

  SSC Guru

  Points: 259015

  More actions

  August 25, 2010 at 7:54 am

  #1211644

  Raunak Jhawar (8/24/2010)

  ---

  You have two options:

  1. Implement an EST(Execute SQL Task)

  2. Implement a Script Task with embedded .NET code

  I would go with option 1. You don't need .NET to execute basic T-SQL.

  Just make sure you have the right connection selected in the Execute SQL Task, copy paste your T-SQL script and then your ready to go.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• wildh

  SSCarpal Tunnel

  Points: 4370

  More actions

  August 27, 2010 at 1:49 am

  #1212791

  If these SQL statments are going to be run a lot you could put them in a sproc.

  The execute SQL task can also execute sprocs.