He has covered the following basic aspects of SSIS
- Opening BIDS environment with a -NOSPLASH
- Creating a small package with some package requirements -- Control flow, Data flow, Event Handler, package explorer
- Deplying the created package
- Scheduling a package
Here are my takeways from this session
You can use -NOSPLASH in the shortcut of the Visual studio icon to avoid the initial start up screen when you open Visual Studio
Data Connections can be created in two ways --
- Via the Data Source on the right hand side of the screen of the solution. If the data source is created this way, it can be used by all the packages in the project.
- Via the connection manager within the package itself. If this is used, the connections can be used only within that package.
Data Flow Taks can be created in two ways
- Via the Control flow tab -- by dragging the Data flow task from the toolbox
- Via the Data Flow Tab -- by just clikcing on the link available in this tab.
Always use OLEDB source instead of ADO.NET source for greater performance
In the OLEDB source tool always use SQL command instead of Table/view. Using the Table/View can slow down the SSIS package considerably.
When sql command in used, use the Preview button instead of the Parse Query button
You can use look up task and derived column task to redirect the un match rows to an error output.
If you use the row count task in the data flow tab that creates an unnamed variable, to name this varaible right click on the grey space on the control flow tab and name the variable.
You can right click on the control flow tab and click on the Package Configurations to configure the package. These configurations can be created as an XML file which can then be modified at deployment.
When you build a project and deploy, the package will be in the MSDB database of the sql server.
You can schedule an SSIS package using the SQL SERVER Agent or using dt exec command line.