December 4, 2013 at 10:02 am
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
December 5, 2013 at 6:31 am
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
December 5, 2013 at 11:45 am
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]
;
December 5, 2013 at 11:03 pm
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
December 6, 2013 at 7:21 am
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