Need help in confiruring a ssis package

  • Hi,

    all I am new to ssis and I want some info on how to create a ssis package here is the scenario.

    server a server b

    table 1 table 10

    table 2 -------------->

    table 3

    table 4

    table 5

    the table 10 in server b is created by a script which was run once and by joining multiple tables in server a,

    here is my task I have to create a ssis package that repeatedly up dates the table 10 on server b.

    if there are any other approach for this I want to know that too

    Thanks' all

  • harita (12/4/2013)


    I am new to ssis and I want some info on how to create a ssis package here is the scenario.

    server a server b

    table 1 table 10

    table 2 -------------->

    table 3

    table 4

    table 5

    the table 10 in server b is created by a script which was run once and by joining multiple tables in server a,

    here is my task I have to create a ssis package that repeatedly up dates the Table 10 on server b.

    if there are any other approach for this I want to know that too

    Without any DDL, it's hard to give any direct help. I'm assuming you have linked servers. From what you're saying, I'm guessing that Table10 was created by doing something like:

    SELECT *

    INTO ServerB.Database.dbo.Table10

    FROM dbo.Table1 t1

    INNER JOIN dbo.Table2 t2 ON t1.field = t2.field

    INNER JOIN dbo.Table3 t3 ON t2.field1 = t3.field

    ...

    You could certainly update using an SSIS package; what specific question did you have?

    HTH,

    Rob

  • thank you for the reply ,

    I have more questions like---this is the actual code to create the table and want to use in a ssis package, is the best practice to keep the tables in sync (INSERT or UPDATE )

    --DROP TABLE [TABLE1].[DBA].[ACCOUNT_CODE];

    /* Create ACCOUNT_CODE Table */

    SELECT

    pc.ACCOUNT_CODE

    , pc.ACCOUNT_CODE_DESCR

    INTO [TABLE1].[DBA].[ACCOUNT_CODE]

    FROM ( SELECT DISTINCT dpc.ACCOUNT_CODE, dpc.ACCOUNT_CODE_DESCR

    FROM ( SELECT DISTINCT

    UPPER(ACCOUNT) AS ACCOUNT_CODE

    , UPPER(ACCOUNT_DESCR) AS ACCOUNT_CODE_DESCR

    FROM [TCB_STAGE].[DBA].[Stage_GL_Balance]

    UNION

    SELECT DISTINCT

    UPPER(ACCOUNT) AS ACCOUNT_CODE

    , UPPER(ACCOUNT_DESCR) AS ACCOUNT_CODE_DESCR

    FROM [TCB_STAGE].[DBA].[Stage_GL_Journals]

    UNION

    SELECT DISTINCT

    UPPER(ACCOUNT) AS ACCOUNT_CODE

    , UPPER(ACCOUNT_DESCR) AS ACCOUNT_CODE_DESCR

    FROM [TCB_STAGE].[DBA].[Stage_Payment_Vouchers]

    ) dpc

    ) pc

    ;

    ALTER TABLE [TABLE1].[DBA].[ACCOUNT_CODE] ALTER COLUMN [ACCOUNT_CODE] INTEGER NOT NULL;

    ALTER TABLE [TABLE1].[DBA].[ACCOUNT_CODE] ALTER COLUMN [ACCOUNT_CODE_DESCR] VARCHAR(30) NOT NULL;

    ALTER TABLE [TABLE1].[DBA].[ACCOUNT_CODE]

    ADD CONSTRAINT [ACCOUNT_CODE_PK] PRIMARY KEY CLUSTERED

    (

    [ACCOUNT_CODE] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ;

  • OK, so now your table is created and exists in the form you want - and you want to insert any new rows from source, or update those with new source data.

    This process is described well in the 'Stairways' section of this site.

    See Adding Rows in Incremental Loads - Level 3 of the Stairway to Integration Services[/url]

    and

    Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services[/url]

    to get you going.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • yes you are right, want to insert any new rows from source, or update those with new source data.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply