Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
SSIS 2005 - Create tables in db using queries in file with ext .sql

已答复 SSIS 2005 - Create tables in db using queries in file with ext .sql

  • Wednesday, February 17, 2010 7:05 PM
     
     
    Sign In to Vote
    0

    Hello

    I have sql quries (create table) in files (.sql) one for each table and i need to go through each and create tables in the database.
    how can i accomplish this?

     

All Replies

  • Wednesday, February 17, 2010 7:16 PM
     
     Proposed
    Sign In to Vote
    0
    Use FoeEachFile to read the file data in script task and store this DDL script in a SSIS variable.
    Use this variable in Execute SQL Task.
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    http://quest4gen.blogspot.com/
     
  • Wednesday, February 17, 2010 8:11 PM
     
     
    Sign In to Vote
    0


    can you share a sample using ForEachFile and code in script task

    for example i have below in .sql file

    CREATE TABLE IN_AIG(
        MESSAGE_ID VARCHAR2(255) DEFAULT '',
        PARENT_ID VARCHAR2(255) DEFAULT '',
        AIG_1_ID INTEGER,
        AIG_2_ACTION_CODE VARCHAR2(255),
        AIG_3_RESOURCE_ID VARCHAR2(255),
        AIG_3_RESOURCE_TX VARCHAR2(255),
        AIG_3_RESOURCE_COD_SYS VARCHAR2(255),
        AIG_4_RESOURCE_TYPE_ID VARCHAR2(255),
        AIG_4_RESOURCE_TYPE_TX VARCHAR2(255),
        AIG_4_RESOURCE_TYPE_COD_SYS VARCHAR2(255),
        AIG_5_RESOURCE_GROUP_ID VARCHAR2(255),
        AIG_5_RESOURCE_GROUP_TX VARCHAR2(255),
        AIG_5_RESOURCE_GROUP_COD_SYS VARCHAR2(255),
        AIG_6_RESOURCE_QTY DOUBLE PRECISION,
        AIG_7_RES_UNITS_ID VARCHAR2(255),
        AIG_7_RES_UNITS_TX VARCHAR2(255),
        AIG_7_RES_UNITS_COD_SYS VARCHAR2(255),
        AIG_8_START_DT DATE,
        AIG_11_DURATION DOUBLE PRECISION,
        AIG_12_DURATION_UNITS_ID VARCHAR2(255),
        AIG_12_DURATION_UNITS_TX VARCHAR2(255),
        AIG_12_DURATION_UNITS_COD_SYS VARCHAR2(255),
        AIG_13_SUBST_CODE VARCHAR2(255),
        AIG_14_FILLER_STATUS_ID VARCHAR2(255),
        AIG_14_FILLER_STATUS_TX VARCHAR2(255),
        AIG_14_FILLER_STATUS_COD_SYS VARCHAR2(255),
        PRIMARY KEY (MESSAGE_ID,PARENT_ID,AIG_1_ID));

     
  • Wednesday, February 24, 2010 9:41 AM
     
     
    Sign In to Vote
    0

    Hello

    I have sql quries (create table) in files (.sql) one for each table and i need to go through each and create tables in the database.
    how can i accomplish this?


    Hi Harris001,

    Create a batch file that execute .sql files using sqlcmd command. This batch file can have input parameter as database name, server name and step from to start the execution.

    find the sample code posted below.

    @ECHO OFF
    SET STEPNO=1

    REM SET SERVER=%1
    REM SET DB=%2
    REM SET STEP=%3

    :PRE1
    SET /P SERVER=[Enter Database Server Name ] :

    IF %SERVER%"dummy" == "dummy" (
        echo No server name passed.;
        GOTO :PRE1
    )

    :PRE2
    SET /P DB=[Enter Database Name ] :

    IF %DB%"dummy" == "dummy" (
        echo No database name passed.;
        GOTO :PRE2
    )


    SET /P STEPNO=[Enter the start step no ] :

    IF %STEPNO%"dummy" == "dummy" (
        echo No arguments passed. starting from STEP 1;
        SET STEPNO = 1
    ) ELSE (
        echo Argument is : %STEPNO%
    )


    PAUSE
    GOTO STEP%STEPNO%

    :STEP1
    echo Now running STEP :%STEPNO%,'SCHEMA_SCRIPT_0.sql'
    sqlcmd -S%SERVER% -d%DB% -E -i"SCHEMA_SCRIPT_0.sql" -o"error_output.txt" -b
    IF NOT %errorlevel%==0 GOTO :ERROR
    SET /A STEPNO = %STEPNO%+1
     
    ECHO "All SQL Sripts have run successfully"


    PAUSE
    EXIT
    :ERROR
        echo "ERROR ENCOUNTERED AT STEP :"%STEPNO%
    PAUSE

    Hope this will solve your problem.

    Thanks,
    Mayur
     
  • Wednesday, February 24, 2010 10:01 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
    Use FoeEachFile to read the file data in script task and store this DDL script in a SSIS variable.
    Use this variable in Execute SQL Task.
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    http://quest4gen.blogspot.com/

    Or, even better, loop over the files using the For Each Loop's File Enumerator, set a File ConnectionManager's ConnectionString property to the enumerated value and set the Execute SQL Task's property SQLSourceType="File Connection". Thus the SQL will be read directly out of the file.
    This will mean you don't have to use the Script Task to do the nasty reading of the file and storing it in a variable.
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
     
  • Thursday, February 25, 2010 6:45 AM
    Moderator
     
     Proposed
    Sign In to Vote
    0

    Hello

    I have sql quries (create table) in files (.sql) one for each table and i need to go through each and create tables in the database.
    how can i accomplish this?

    I've got a blog post (with a link to a video) that shows how you can use the Execute SQL Task to execute .sql scripts:
    .DoNotDisplay { display: none; }
    Hope that helps.
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me