Friday 24 April 2015

Dynamically change table structure in SSIS with Example



Solution: The Solution of the above problem is solved by using Script Component, Execute Sql tasks and Dataflow task.


• Create two SQL tables which will store the data Source and Destination information.

CREATE TABLE [dbo].[EmpData](
[Eid] [int] NOT NULL,
[Ename] [nvarchar](50) NULL,
[Sal] [decimal](18, 0) NULL
)

CREATE TABLE [dbo].[Dest_EmpData](
[Eid] [int] NOT NULL,
[Ename] [nvarchar](50) NULL
)
And add the data depends on requirement.

• Sequence Container is used to created a subset of a package. The subset is contains three execute sql tasks, there are successfully completed after dataflow task is executed.

• The first Execute SQL Task 1 which reads [dbo].[EmpData] source table data from database and calculate the columns count and stores the value in the package parameter
[User]::[ Source_Col_Count] by using below query.


• The second Execute SQL Task 2 which reads [dbo].[Dest_EmpData] destination table data from database and calculate the columns count and stores the value in the package parameter
[User]::[ Source_Dest_Count] by using below query.


• After execute the two tasks then we check condition by using precedence constraint.The result of precedence constraint is true then executes task of Execute SQL Task 3.


• The Third Execute SQL Task 3 is used to change the structure of table by using Alter command as follows


• After completion of Sequence Container we have attached Data Flow Task, it is used to pass data from Source [dbo].[EmpData] to [dbo].[Dest_EmpData] by using OLE DB Source and Destination Components.


Note: When u run package, it shows one error at the time of validation OLE DB Destination is failed due to table structure is different. The Error message is shown below


To avoid the above error we can change the package properties, in Dataflow Task àPropertiesàSet [Delay Validation] property to True.



 Let me know what you think about this article.

2 comments:

  1. excellent article keep it up

    ReplyDelete
  2. Too Good article,keep sharing more posts with us.
    Thank you...

    MSBI Training

    ReplyDelete