PARAMETERS IN SSIS
Task name: How to create parameters in SSIS
Parameter:- SSIS parameters help to ease the process of passing runtime values to SSIS packages
There types of parameters:
1.)package parameters: package parameters are scoped at package level.
2.)project parameters: project parameters are scoped at SSIS project level.project parameter can be used in any package in the SSIS project.
Project does not contain expression property like variables.
Procedure:-
- we need to create package under integration services project3
- Under solution explorer we need to rename the package as required. i.e param.dtsx.
- Drag and drop the data flow task from the ssis toolbox to the control flow.
- double click on data flow task and drag and drop the OLEDB source from the ssis tool box to the data flow task.
- Click on the parameters in the package and specify the name of the parameter and data type and give the parameter value as required.
- Configure the OLE DB source by specifying the OLEDB connection manager and select the data access mode to SQL command and give the syntax as shown below
- Click on the parameters and specify the parameters name and select the package parameter and click on ok.
- Drag and drop the flat file destination from the ssis toolbox to the data flow task.
- Click on the flat file destination and specify the connection manager name and browse the target file from the file manager as shown below.
- Click on the mappings and check that all the input columns are mapped with the destination column and click on ok.
- Click on start and execute the package here package got executed
- Here as we have assigned the value to the parameter as zipcode 11001 so the target file will be loaded with the data with the same zipcode.
