2016-SSIS-1-Import data from excel into SQL Server using SSIS

Import data from excel into SQL Server using SSIS

a.xls <- not Microsoft latest versn. The latest is .xlsx

Id    Name
1     Keshav
2     Gaurav
3     Kalpana

CREATE TABLE EMP_A(Id INT,Name NVARCHAR(500),Dept VARCHAR(10) DEFAULT 'IT')

CREATE TABLE EMP_B(Id INT,Name NVARCHAR(500),Dept VARCHAR(10) DEFAULT 'HR')

Create an Integration Services project.

In Solution Explorer-Properties-Debugging -
Run64BitRuntime - false


ControlFlow:

Sequence Container

DataFlow Task<-Property(DelayValidation=True)
                                     




Right Click - when in the control flow tab.Choose Variable.
Variables:

Name          Scope        Data Type      
ExcelPath    Package    String
TableName  Package     String

Value
C:\Keshav\A\a.xls
EMP_A





To make package dynamic , in the "Data Flow Task" ,properties, the "DelayValidation" property is set to "True".



The prevalidation check will not happen, the values will be checked at the run time ( for the package path, table name).

----
Data Flow Task:DFT




In the Data Flow Task , "ValidateExternalMetadata" , set it to "False".

Note:
In the Control , it is set to "DelayValidation" True , in the Data Flow Task in the "Excel Source", "ValidateExternalMetadata" to false,the equivalent.

Right Click - "Excel Source" - Choose "Edit".Then set up the configuration details.




















Connection Manager:
Excel Connection Manager <-Right click - choose "Expression", choose
the "ExcelFilePath" from the drop down box.





Drag the OLEDB Destination.Set the "ValidateExternalMetaData" to False..

Right Click on the OLE DB Destination <- Edit







Dynamic:

Right click->Package Configurations







Click on Browse:->



Type the .Config name that you want to create for this xml configuration.



What things we want to make dynamic, the variable values.







Quick tip:to look at the dtsconfig file path.



Run command:paste it





Open the same file in notepad.



To Deploy the package.
On the solution explorer - properties- CreateDeploymentUtility-True


Build the package.Under "Bin" directory , creates a "Deployment" folder.

Deployment folder has 3 files.



Deploy using the Manifest file.


Double click the Manifest file, it will ask "File System" or MSDB.



yes







Folder content what is there?