Wednesday, April 28, 2010

SSIS session in Auckland by Patrick

We had a really interesting session with Patrick of Pragmatic Works yesterday on the topic "Introduction to SSIS".

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

Tip 1

You can use -NOSPLASH in the shortcut of the Visual studio icon to avoid the initial start up screen when you open Visual Studio

Tip 2

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.

Tip 3

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.

Tip 4

Always use OLEDB source instead of ADO.NET source for greater performance

Tip 5

In the OLEDB source tool always use SQL command instead of Table/view. Using the Table/View can slow down the SSIS package considerably.

Tip 6

When sql command in used, use the Preview button instead of the Parse Query button

Tip 7

You can use look up task and derived column task to redirect the un match rows to an error output.

Tip 8

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.

Tip 9

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.

Tip 10

When you build a project and deploy, the package will be in the MSDB database of the sql server.

Tip 11

You can schedule an SSIS package using the SQL SERVER Agent or using dt exec command line.

No comments:

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  -- create table #...