locked
Create DDL Stored Procedure RRS feed

  • Question

  • I want to create a stored procedure to drop and recreate tables prior to loading them from other tables. I.e. the Execute SQL Task would be the first step of an SSIS package before some data flow tasks.

    Essentlially I'm refreshing a datamart. I have a script that works fine to drop and recreate the tables, but want to keep it external from the ETL package in a stored proc. I tried pasting the script into a stored proc shell and it wouldn't compile.  It didn't like the "if table exists drop it" code for some reason inside the proc.

    Secondly, assuming the Stored Proc gets created, are these properties correct for Execute SQL Task in SSIS:

    ------------------

    Connection type: Ado.net

    SQL Source Type: Direct Input

    SQL Statement: exec [Schema].[sp_xyz]

    IsQueryStoredProcedure: True

    ------------------

    Or do I need to specify dummy parameters as some threads indicate?

    Thirdly, one thread indicated that Control Flow steps may not execute in sequence (?) and that I might need to specify "Delay Validation" somewhere.  If so, where do I do that?

    Thanks much,

    Jnana


    Jnana Sivananda

    Friday, March 8, 2013 7:02 PM

Answers

  • Hi Jnana,

    -----------------------------------------------------------------------------------

    Connection type: OLE DB

    Connection : set the connection string pointing to your database

    SQL Source Type: Direct Input

    SQL Statement: exec [Schema].[sp_xyz]

    IsQueryStoredProcedure: True/False (I tested both worked for me)

    -----------------------------------------------------------------------------------

    If your below tasks are referring to Objects(Tables) which are to be created in the above tasks and if it does not exists in the database ,

    you will get an error "Invalid Object Name" to avoid that right-click on task referring object which are yet to be created and set the Delay Validation - True

    Example for Delay Validation http://sathyadb.blogspot.in/2012/09/sql-server-integration-services.html

    -----------------------------------------------------------------------------------


    Thanks & Regards, sathya

    • Proposed as answer by Sorna Kumar Muthuraj Saturday, March 9, 2013 10:09 AM
    • Marked as answer by Jnana Monday, March 11, 2013 3:22 PM
    Saturday, March 9, 2013 9:18 AM

All replies

  • What is the script you are using?  Does the script in your stored procedure work if you run it as a plain query? 

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Yubo. Zhang Monday, March 11, 2013 4:26 PM
    • Unmarked as answer by Yubo. Zhang Monday, March 11, 2013 4:26 PM
    Friday, March 8, 2013 8:16 PM
  • Hi Jnana,

    -----------------------------------------------------------------------------------

    Connection type: OLE DB

    Connection : set the connection string pointing to your database

    SQL Source Type: Direct Input

    SQL Statement: exec [Schema].[sp_xyz]

    IsQueryStoredProcedure: True/False (I tested both worked for me)

    -----------------------------------------------------------------------------------

    If your below tasks are referring to Objects(Tables) which are to be created in the above tasks and if it does not exists in the database ,

    you will get an error "Invalid Object Name" to avoid that right-click on task referring object which are yet to be created and set the Delay Validation - True

    Example for Delay Validation http://sathyadb.blogspot.in/2012/09/sql-server-integration-services.html

    -----------------------------------------------------------------------------------


    Thanks & Regards, sathya

    • Proposed as answer by Sorna Kumar Muthuraj Saturday, March 9, 2013 10:09 AM
    • Marked as answer by Jnana Monday, March 11, 2013 3:22 PM
    Saturday, March 9, 2013 9:18 AM
  • The script works fine independently in the query window of SSMS. The script is just a series of

    ------

    if table name exists, drop it.

    CREATE TABLE schema.name

    -----

    after a

    USE DBNAME

    GO

    Thanks,

    Jnana


    Jnana Sivananda

    Saturday, March 9, 2013 2:15 PM
  • Apparently the "GO" statements inbetween the commands have to be taken out of the script for the stored proc to compile. Researching, I see "GO" isn't a T-SQL command, but a message to process a batch of commands.

    Thanks,

    Jnana


    Jnana Sivananda

    Monday, March 11, 2013 3:13 PM
  • Thanks, Sathya. Worked great.

    Jnana Sivananda

    Monday, March 11, 2013 3:23 PM