advertisement

Advanced ETL SSIS 2012 & Talend

33 %
67 %
advertisement
Information about Advanced ETL SSIS 2012 & Talend
Technology

Published on March 12, 2014

Author: snnadozie

Source: slideshare.net

advertisement

Advanced ETL SSIS 2012 & Talend By Sunny Okoro

1

2

3

4

5

6

7

8

9

10

11 Example of Flat files Creation

12

13 The connection string ensures that file is created in the right folder with the right name as declared in the SSIS variable.

14

15

16

17

18 Example of Pivot Creation

19

20 This data flow task contains many tables, files, aggregations and derived columns not all will be illustrated. The pervious demonstrations illustrate some of the key components in this data flow. The following illustrations demonstrates major expression used in derived columns to transform the data.

21

22

23

24 The stored procedure executed from SQL Server management studio displays null data that would be transformed to a specific value using expression in SSIS.

25

26 Countrycode = AU [ AUSTRIALIA] STATECODE= VIC[VICTORIA] EXECUTION

27

28

29

30

31

32 Results Abridged

33 Results Abridged

34

35 Results Abridged

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50 Results Abridged

51

52

53

54

55

56 Results Abridged Results Abridged

57

58 The countrycode is changed to US for USA and Statecode to CA for this execution. The [SalesRpt_FiscalYr_City] table does not contain any austrialian cities from the pervious demonstration because the table was truncated at the beginning of each package execution The countrycode remained the same but the statecode was changed to IL. The data contrnts for the state of Illinios where created in the same folder as state contents for Victoria. The prefixes were changed to IL for each file name to reflect the countrycode and statecode which was done using file connection strings.

59

60 No data found for the city which was in California in the previous execution of this package. I will change the countrycode to CA and state code to BC .

61

62 The output folder is clustered and SSIS will delete every content in the output folder at the beginning of each execution.

63

64 The pervious content has been deleted by SSIS using the file system task which can also be utilized to create directories, copy files etc. The output folder has no content for Great Britain.

65

66

67

68

69

70

71

72 For this demonstration, Talend ETL application would be utitlized to transform the data into xml format that can be recognized by SSIS.

73

74

75 Data Mapping

76

77

78

79

80

81 The Adoworks XML document and the Adworks XSD document are created in the XML folder.

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114 Data Validation Only the pivot based reports are displayed fully. The rest of reports are snapshots not the entire data extracted from the database.

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133 Another way to create the XML format is to use TSQL XML features like XML Auto and Elements to parse the Query result into an XML Format and extract into an XML file which can be read by SSIS. This method is much faster for smaller data not for big data in a laptop environment.

134

135

136

137

138

139

140

141

142

143

144

145 All of the results are abridged

146 Instead of inserting data for all country when the package is executed. SSIS will insert data using the county code and state code highlighted above and the additional countrycode to determine which table to populate

147 Only the Australian table is populated. The reaming tables were ignored because the condition of the expression on the conditional split did elevate to true

148

149 Australian Customer data All of the results are abridged

150

151 Canadian Customer All of the results are abridged

152 American Customer All of the results are abridged

153

154

155

156

157

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

Advanced ETL MS SSIS 2012 & Talend - Technology

1. Advanced ETL -SSIS 2012 & Talend By Sunny Okoro . 2. 1 Contents Database Systems ...
Read more

ETL - Talend - Open Source Integration Software for the ...

Powerful Open Source ETL from Talend. ... Advanced ETL functionality including string manipulations, automatic lookup handling, ...
Read more

Advanced ETL Processor - Overview | Advanced ETL Processor ...

Advanced ETL Processor is a powerful data integration application with support for transforming data with virtually any formats
Read more

ETL for Analytics | Talend

Talend ETL for Analytics. ... Talend Enterprise Big Data adds teamwork, advanced management features, indemnification and support. Learn More.
Read more

SSIS 2012: Creating a Simple ETL Package Tutorial - SSIS ...

SSIS 2012: Creating a Simple ETL Package Tutorial MSDN Blogs ... The tutorial documentation is at SSIS Tutorial: Creating a Simple ETL Package.
Read more

Advanced ETL Processor Enterprise - Overview | Advanced ...

Advanced ETL Processor ... Unlike most other ETL products, Advanced ETL Processor ... Advanced ETL Processor Enterprise runs on Microsoft Windows 2008/2012 ...
Read more

Open source ETL tools

... are currently evaluating or deploying Open Source ETL tools like Talend, ... many open source ETL tools still can’t beat the leading closed ...
Read more

Ssis (2012) | LinkedIn

View 946 Ssis (2012) posts, presentations, experts, and more. Get the professional knowledge you need on LinkedIn.
Read more