SSIS Designer is extremely easy to use and gives a user the flexibility
of editing and manipulating any of the package properties in any order
needed, as opposed to the strict sequential order of the SSIS Wizard.
After you have mastered all the package concepts, you will find that
you will be spending most of your time using the SSIS Designer instead
of the wizard.
Because you have
already created an SSIS package using the wizard, you can just open a
version of this package (which you stored in the filesystem as a .dtsx file) with the SSIS Designer to see some of the SSIS Designer’s capabilities (see Figure 1). You simply locate a .dtsx package file (such as HotCustomersPlus.dtsx)
using the File Open option within (BI) Development Studio/Visual
Figure 1. The SSIS Designer: opening the HotCustomersPlus.dtsx package.
As you can see with the
SSIS Designer, you are within the common Visual Studio IDE environment,
which is used for any type of managed code. SSIS package creation is
now just another option of a code development project. The SSIS
Designer includes a main designer pane, a palette of toolbox icons to
the left, an error pane on the bottom, the Solution Explorer to the
right, and Properties pane in the bottom right. The Connection Manager
sits directly below the designer pane, and there are four basic tabs in
the designer pane for different purposes: the Control Flow pane is for
overall task, control of flow, and constraint specification; the Data
Flow pane is for generating and manipulating the data mapping and
transformation itself; the Event Handlers pane is for defining what
error handling needs to be part of this package; and the Package
Explorer pane is for an overall view of the elements of the package.
The SSIS Designer is truly a
point, click, and drag working environment. For anything in the
workspace, you simply click the icon, such as DestinationConnectionOLEDB
(in the Connection Managers pane) or the Preparation SQL Task icon in
the Control Flow pane to see its properties, or you click the solid
line between the Preparation SQL Task and Data Flow Task boxes to see
the task constraints and workflow defined for the package. If you
haven’t created the HotCustomersPlus SSIS package, you should do so now with the wizard and save it to the filesystem as well (as a .dtsx file). You use it in the next example.
At this point, you need
to fire up Visual Studio 2008 or the BI Development Studio environment.
Either way, the same IDE is started. The SSIS Designer is initiated
within this IDE. When you have successfully started the Visual Studio
2008 IDE environment, you can easily open the SSIS package you just
created and use it to get familiar with the SSIS Designer. Simply
choose File, Open in Visual Studio 2008 and locate the HotCustomersPlus.dtsx SSIS package you created earlier (see Figure 2). You are about to modify this SSIS package to more fully support the HotCustomersPlus data mart and Excel comma-delimited flat file creation requirements because the wizard could not completely do that.
Figure 2. Opening the HotCustomersPlus.dtsx package.
When you open this package,
you enter Visual Studio’s development environment, where you can use
the SSIS Designer capabilities. You will be using the SSIS Toolbox to
the left to add functionality to this small SSIS package so that it
will completely fulfill the data mart requirements outlined earlier. If
you look back at Figure 52.30,
you see this simple SSIS package within the SSIS Designer. This is what
you should have as well. Now, you can modify any existing tasks or add
others to this SSIS package. If you recall, you originally set up this
package to create a new destination table (on the other SQL Server
instance) as the first step. Because you already executed this once,
that table now exists (HotCustomersPlus
on the destination SQL Server instance). Therefore, you need to change
this first step to truncate the destination table instead of
re-creating it each time. In addition, you need to add another task to
this package that will spin off newly populated data (from the
destination table) into an Excel comma-delimited flat file that can be
easily distributed to the sales team. As you change this package, you
also re-label the tasks to be more reflective of what they are doing
(and not use the default task naming that the wizard used).
The sales team is waiting, so follow these steps:
don’t need the Drop Table(s) SQL Task 1 step because you will be
utilizing the existing table created from the wizard. So, first just
delete this step. You can use a truncate table approach to clear the
existing table out before repopulating it each time the package is run.
Simply right-click on the Drop Table(s) SQL Task 1 box and choose
Delete. Confirm you want to delete it.
on the Preparation SQL Task and choose Edit. The Execute SQL Task
editor comes up, and in it you can see all aspects of this SQL task.
Click the SQL statement property within this window (where you see the CREATE TABLE statement) and then click the ... icon to the right of the CREATE TABLECREATE TABLE statement to a TRUNCATE TABLE
statement. This opens an editor window that contains the full SQL statement. Now, change this statement for the same table on the destination SQL Server instance:
TRUNCATE TABLE [UnleashedDataMart].[dbo].[HotCustomersPlus]
This statement is clearly shown in Figure 3. After updating the SQL statement to a TRUNCATE, change the BypassPrepare True/False flag to False.
Then click OK and click Parse Query to make sure the SQL statement is
valid. You can now rename this task to something more appropriate by
just clicking the Name property of this task and changing it to something like Clear out all rows in Destination Table.
If all is well, click OK to exit this window. Now this task clears out
the destination table before it repopulates it with new data instead of
re-creating the destination table over and over.
Figure 3. Modifying the Execute SQL Task from a CREATE TABLE statement to a TRUNCATE TABLE statement.
Rename the existing Data Flow task that pulls data out of the source SQL Server tables via a SELECT
statement and populates the destination table. To do so, click this
current data flow task and either right-click and choose Edit or just
click the Data Flow tab in the IDE. You now see the multiple steps
within this data flow. Locate the Name property of this data flow task and rename it from Data Flow Task to Extract from Source Tables, Populate to Destination table. Click the first step of the data flow (which has the name property Source – Query) and rename it Select orders from AdventureWorks2008. Now, click on the destination task (Destination – HotCustomersPlus) and rename it Populate Destination Table – HotCustomersPlus, as shown in Figure 4.
Figure 4. Modifying the data flow task within the SSIS package.
a new data flow task that will read the sales order data from the
destination table being populated from the source tables and then write
out an Excel flat file with this new data. From the Control Flow tab of
this SSIS package, drag a new data flow task (from the Toolbox on the
left) out to the Control Flow designer pane and then modify its name
property to be Read from Destination table, Populate Excel flat file, as shown in Figure 5.
Figure 5. Creating a new data flow task to write data out to an Excel flat file.
the Data Flow tab, and you are in the Data Flow designer pane. Nothing
is there yet (the Data Flow design space is empty). Also note that the
Toolbox entries change when you click this tab (they are now all the
data flow task items). Drag an OLE DB Source item from the
Toolbox over to the Data Flow designer pane. You will use this to get
the data from the destination table. Rename this Data Flow step
something like Pull data from Destination Table and then
right-click this new Data Flow source task and choose Edit. This puts
you in the OLE DB Source Editor, where you can identify which
connection manager to use (DestinationConnectionOLEDB, in this example) and what table you want to get data from (HotCustomersPlus table). You want the whole table, so specify the Table or View option for the access mode (see Figure 6).
Click the Preview button at the bottom of this editor to verify that
you will get all data from the destination table. Clicking OK returns
you to the Data Flow designer pane.
Figure 6. Specifying the Source Data Flow items for a new data flow task.
Back in the Data Flow designer pane, scroll down in the Toolbox to the Data Flow Destinations portion and locate the Flat File Destinations item. Drag this over to the Data Flow designer pane and rename it something like Write data to Excel Flat file.
you go any further, you need to connect the source data flow task (and
its data output) to this new Excel flat file destination. You can
easily do this by just clicking the source data flow task’s outbound
arrow (that is just below the box and is green) and dragging it to the
new Excel flat file destination box. A full arrow is redrawn that
connects these two data flow tasks (as you can see in Figure7).
Figure 7. Connecting the data source to the data destination for the new data flow task.
this new data flow task item (Excel Flat file Destination) and choose
Edit. This again puts you in an editor where you can specify the flat
file destination file properties you want. This starts with identifying
the connection manager and the flat file to be used. Click New here and
choose the Delimited format of the destination flat file. Now, specify
a location and filename for the destination flatfile (HotCustomersPlus.csv). Figure 8
shows this complete flat file destination specification. Click the
Preview button to make sure this data will be retrieved properly
(column names appear across the top of the preview dialog; because the
file is empty, no data shows).
Figure 8. Specifying the flat file destination data flow items for a new data flow task.
You can also click the Mappings option in the Flat File Destination Editor dialog. As you can see in Figure 9, each of the columns in the source table (the HotCustomersPlus table) will be mapped, one to one, to the flat file columns with the same names.
Figure 9. Source and destination column mappings.
to the Control Flow pane of the SSIS package and connect the new data
flow task to the prior one. You do this by clicking the original data
flow task and grabbing its control of flow arrow beneath the box and
dragging it to the new data flow task you just created. Accept the
default to execute the task on success of the prior task, as shown in Figure 10.
Note that the Flat File Destination connection manager now appears
under the connection manager pane, and you should have zero errors in
the error list. At this point, save the package by clicking the disk
icon or selecting File, Save.
Figure 10. Control of flow between the old data flow and new data flow tasks.
To execute the package, double-click the .dtsx
file, which automatically invokes the package execution utility. Choose
Execute, and the package executes and shows all results in the
execution console, as shown in Figure 11. That’s it: you have populated the data mart and created data in an flat file for distribution to the sales team.
Figure 11. Executing the SSIS package.
You could also execute this new package by using the dtexec utility at a command prompt:
C:> dtexec /FILE "C:\HotCustomerPlus.dtsx"