Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in confiruring a ssis package Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 10:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:54 PM
Points: 95, Visits: 188
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
Post #1519704
Posted Thursday, December 5, 2013 6:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:47 PM
Points: 1,170, Visits: 1,211
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
Post #1520017
Posted Thursday, December 5, 2013 11:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:54 PM
Points: 95, Visits: 188
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]
;
Post #1520264
Posted Thursday, December 5, 2013 11:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 5,047, Visits: 11,799
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

and

Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services

to get you going.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1520416
Posted Friday, December 6, 2013 7:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:54 PM
Points: 95, Visits: 188
yes you are right, want to insert any new rows from source, or update those with new source data.

Post #1520578
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse