Sql-server – Export multiple tables among servers

sql serverssis

We currently have an SSIS package that exports about 70 large tables nightly, with truncate and load at the destination. Our tables keep changing, we sometimes add some tables and remove some and editing the SSIS package accordingly is tiring.

Is there a way to have a text file or a table with the list of table names in it and have an SSIS package to loop the table and perform the export? That way, it is easier to handle dynamic list of tables that get exported.

Any links, pointers are appreciated.

Best Answer

what you are looking for is to dynamically create tables in the destination. or in other words Copying tables from Source to destination programmatically

here is a very useful article that transfers tables from access to sql dynamically but you can take the same concept and apply it on your source as well.

https://stackoverflow.com/questions/6298806/how-do-i-programmatically-get-the-list-of-ms-access-tables-within-an-ssis-packag

Related Solutions

Sql-server – SQL Server to Oracle Export – Data conversion error

I tried your script and recreated your environment completely and i didn't see any error.

Ran perfectly and created all tables and transferred all data successfully

i tried running under a sql agent job and still no error was generated

but i guess you can update your insert script by making sure the column gets converted under all conditions

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE Null END

change your script to

INSERT INTO OPENQUERY (
    ZMMS_ORA 
    'SELECT 
        "COMPANYID",
        "BO_ID",
        "AS CM",
        "AS RSM",
        "AS SE",
        "TS CM",
        "TS RAE",
        "TS RSM",
        "TS SE",
        "BBONE AM",
        "ASM",
        "WCT ASM",
        "LS TSM",
        "WCT TSM",
        "TS TSM",
        "AS PM",
        "AS DEV",
        "TS PM",
        "TS DEV",
        "TS TECH",
        "BB EXEC",
        "CHM",
        "ANGEL Client Manager (CM)",
        "ANGEL Engagement Team",
        "BbOne Client Manager (CM)",
        "Blackboard Executive",
        "CE/Vista Hosting Mgr (ASM)",
        "CE/Vista Tech Support (TSM)",
        "Complex Hosting Manager (CHM)",
        "Learn Client Manager (CM)",
        "Learn Hosting Mgr (ASM)",
        "Learn Reg Sales Mgr (RSM)",
        "Learn Sales Engineer (SE)",
        "Learn Services Developer",
        "Learn Services ICM Mgr",
        "Learn Services Project Manager",
        "Learn Tech Support (TSM)",
        "Transact Client Manager (CM)",
        "Transact Project Manager",
        "Transact Reg Acct Exec (RAE)",
        "Transact Reg Sales Mgr (RSM)",
        "Transact Sales Engineer (SE)",
        "Transact Services Technician",
        "Transact Tech Support (TSM)",
        "Xythos Tech Support (TSM)"  FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
    )
SELECT "COMPANYID"
    ,[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END 
    ,"AS CM"
    ,"AS RSM"
    ,"AS SE"
    ,"TS CM"
    ,"TS RAE"
    ,"TS RSM"
    ,"TS SE"
    ,"BBONE AM"
    ,"ASM"
    ,"WCT ASM"
    ,"LS TSM"
    ,"WCT TSM"
    ,"TS TSM"
    ,"AS PM"
    ,"AS DEV"
    ,"TS PM"
    ,"TS DEV"
    ,"TS TECH"
    ,"BB EXEC"
    ,"CHM"
    ,"ANGEL Client Manager (CM)"
    ,"ANGEL Engagement Team"
    ,"BbOne Client Manager (CM)"
    ,"Blackboard Executive"
    ,"CE/Vista Hosting Mgr (ASM)"
    ,"CE/Vista Tech Support (TSM)"
    ,"Complex Hosting Manager (CHM)"
    ,"Learn Client Manager (CM)"
    ,"Learn Hosting Mgr (ASM)"
    ,"Learn Reg Sales Mgr (RSM)"
    ,"Learn Sales Engineer (SE)"
    ,"Learn Services Developer"
    ,"Learn Services ICM Mgr"
    ,"Learn Services Project Manager"
    ,"Learn Tech Support (TSM)"
    ,"Transact Client Manager (CM)"
    ,"Transact Project Manager"
    ,"Transact Reg Acct Exec (RAE)"
    ,"Transact Reg Sales Mgr (RSM)"
    ,"Transact Sales Engineer (SE)"
    ,"Transact Services Technician"
    ,"Transact Tech Support (TSM)"
    ,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;

Sql-server – Export SQL Server varbinary field to string using Visual Basic script in SSIS

On line 9, instead of GetType(Byte) you need GetType(Byte()).

Encoding.GetString only takes a byte array; never a single byte, so regardless of the type provided by the framework, what's there is logically not going to work.

If there is another field in the table that comes back as a Byte, you'll need to add another branch to the If statement to handle the encoding (or convert the single byte to a byte array of length 1 and use common encoding code).

Best Answer

Related Solutions

Sql-server – SQL Server to Oracle Export – Data conversion error

Sql-server – Export SQL Server varbinary field to string using Visual Basic script in SSIS

Related Question