GENERATING ETL PACKAGES FROM TEMPLATE

• US 20090043778A1
• Filed: 08/08/2007
• Published: 02/12/2009
• Est. Priority Date: 08/08/2007
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Jambunathan, Mahesh, Tawde, Santosh, Beher, Mukeshkumar M.
• Application Number:
  US11/835,629
• Publication Number:
  US 20090043778A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

Packages for server data extraction, transformation and loading (ETL) are generated and managed through package templates. The templates include configurable and static parameters and are created from user input. Package instances having template property values are created from the templates and then transformed into a package used to implement ETL functionality. In some embodiments, a package template may include one or more components for retrieving data from a source, transformation of the retrieved data before it is stored, and storage of the data at a destination. Packages can be modified based by importing the package, changing an instance associated with the modified package, and generating a package from the modified instance.

Claims

1. A method for generating a set of code comprising a package, comprising:

generating a template for a package configured to be used with an SQL server, the template having a source component, data transformation component and a destination component, at least one of the components including one or more properties designated as configurable by a user;

generating an instance from the template, the instance including values for the one or more configurable properties; and

creating a package from the instance, the package configured to retrieve data from one or more sources identified by the source component and store the data in one or more destinations identified by the destination component, the package including a mapping between the one or more sources and the one or more destinations.

2. The method of claim 1, wherein said steps of generating a template, generating an instance, and creating a package are performed in response to user input received through a user interface

3. The method of claim 1, wherein the source component includes at least one configurable property which identifies a data source to retrieve data from.

4. The method of claim 3, wherein the at least one configurable property identifies a server location and a data located at the server

5. The method of claim 1, wherein the data transformation component includes at least one configurable property which identifies a change to be made to data retrieved from the one or more sources.

6. The method of claim 5, wherein the at least one configurable property identifies a change to the format of the data

7. The method of claim 5, wherein the at least one configurable property identifies an operation to perform on the value of the data

8. The method of claim 1, wherein the destination component includes at least one configurable property which identifies a data destination to retrieved store data.

9. The method of claim 8, wherein the at least one configurable property identifies a server location.

10. The method of claim 1, wherein the destination component includes at least one configurable mapping which associates a data source with a data destination.

11. The method of claim 1, wherein the data is stored by a package at an SQL server.

12. The method of claim 1, further comprising:

detecting a change in data associated with a package;

importing the package;

modifying a second instance associated with the imported package; and

modifying the package in response to modifying the second instance.

13. One or more processor readable storage devices having processor readable code embodied on said processor readable storage devices, said processor readable code for programming one or more processors to perform a method comprising:

creating a first package from a template instance, the template instance containing,

a source component configured to retrieve source data,

a data transformation component configured to process the retrieved data, and

a destination component configured to store destination data at an SQL server,

the first package including at least one source component property, destination component property, and a mapping associated with the source data and destination data;

accessing an instance associated with the first package;

changing one or more component properties of the accessed instance; and

creating a modified package based on the changed instance.

14. The one or more processor readable storage devices of claim 13, the method further comprising:

identifying the first package associated with changed source data or destination data; and

importing the first package by a package application.

15. The one or more processor readable storage devices of claim 13, the method further comprising:

changing one or more properties of the destination component of the template instance in response to the change.

16. The one or more processor readable storage devices of claim 13, the method further comprising:

changing one or more properties of the source component of the template instance in response to the change.

17. The one or more processor readable storage devices of claim 13, the method further comprising:

importing the first package;

creating a second instance from the imported package;

providing information for the second instance to a user through an interface;

receiving input associated with modifications to the second instance through the interface, said step of changing performed in response to said step of receiving input associated with modifications;

storing a modified instance; and

creating a modified package from the modified instance,

said step of creating a modified package including,

creating a parameter file from the modified instance; and

creating a modified package from the parameter file and a template associated with the first package.

18. A method for generating a package for processing data, comprising:

generating a template for a package configured to be used with an SQL server, the template having a source component, data transformation component and a destination component, the source component including one or more configurable source properties, the destination component containing one or more configurable destination properties and one or more mapping properties;

generating an template instance from the template, the instance including values for the one or more configurable source properties which indicate a data source, values for one or more configurable destination properties which indicate a data destination, and values for one or more configurable mapping properties which indicate how the source data is mapped to the destination data;

creating a parameter file from the instance, the parameter file indicating changes to be made to a template based on the template instance; and

creating a package from the template and the parameter file, the package configured to retrieve data from one or more sources identified by the source component properties, transform the retrieved data, and store the transformed data in one or more destinations within an SQL server as identified by the destination component properties.

19. The method of claim 18, further comprising:

importing the package;

creating a second instance from the imported package;

providing information for the second instance to a user through an interface;

receiving input associated with modifications to the second instance through the interface, said step of changing performed in response to said step of receiving input associated with modifications;

storing a modified instance;

creating a parameter file from the modified instance; and

creating a modified package from the parameter file and a template associated with the modified instance.

20. The method of claim 18, further comprising:

executing the package to retrieve data from a data source and store the data at a data destination.