This project is read-only.

Install it, Configure-it & Run-it…

Please consider this Post-build event

 

path "C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin"
gacutil /uf "SSISBulkExportTask100"
del "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\SSISBulkExportTask100.dll"
copy "$(TargetDir)SSISBulkExportTask100.dll" "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\"
gacutil -i "$(TargetDir)SSISBulkExportTask100.dll"

First of all, you have to add the component to the ToolBox in the category “Control Flow Items” in the usual way… right click, you choose the “SSIS Control Flow Items”, you search and check “Bulk Export Task” and then click the OK button.

image

Did you see the Icon and Label of the component in the ToolBox?

image

image

.. so, drag it into the design surface

And now create

we will create an ADO.NET Connection for the source and a File Connection For the destination:

image

As you saw the database I use is the classic database AdventureWorks provided by Microsoft.

So, double-click the component and now you will see the component UI.

In this example I already filled the User Interface with some values; please take a look and you will see for what are used all of these parameters.

For more information regarding these parameters take a look at this page of the BCP utility: http://msdn.microsoft.com/en-us/library/ms162802.aspx

 image image

 image image

image

To get out of this generic database I created a stored procedure with a parameter EmailPromotion (int)

The content of the stored procedure is:

USE [AdventureWorks]
GO
CREATE PROCEDURE [dbo].[ContactEmailPromotion]
    @EmailPromotion int
AS
BEGIN
    SELECT [ContactID]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailAddress]
      ,[EmailPromotion]
      ,[Phone]
      ,[PasswordHash]
      ,[PasswordSalt] 
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Person].[Contact]
  WHERE EmailPromotion = @EmailPromotion

GO

Don’t forget a thing, on the interface when you see the symbol f(x), it means that you can use the “Expression Builder” provided by SSIS as you see in the example below:

image

An another important thing is that you can preview of what you will export:

image

As you observed in the window caption, in the preview window you will get the TOP 100 rows!

Please note: That’s an observation that concerns only stored procedures: if your stored procedure returns a column of XML type you cannot preview it !

So… we have a feature: to receive an email with some statistical information.

Under the tab you already saw this:

image

Clicking the “Email details” button, you will get this modal window:

image

So, eventually you add two user variables [EmailFrom] and [EmailTo], you specify a subject and you can edit your email body freely or using an expression by clicking the correspondent f(x) button and don’t forget to add a SMTP Connection.

In the same time you can insert some internal expressions having like source this:

image

So… you choose from ComboBox one of the available items, you click into the TextBox surface and then you click the “Insert” button

…and now you can execute the task. Did you obtained the green box?

image

And the log:

image

Tips & Tricks: What is Enable Command Shell?

EXEC master.dbo.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC master.dbo.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

…that because, implicitly, the system stored procedure xp_cmdshell is not enabled by default!

 

Enjoy-it!

Last edited Aug 8, 2011 at 11:48 AM by cosminfr, version 7

Comments

No comments yet.