Missing Toolbox Items in Business Intelligence Development Studio

SSIS package does not display the controls in toolbox

The solution was to close VS, delete the toolbox*.tbd items under “C:\Users\USERNAME\AppData\Local\Microsoft\VisualStudio\9.0″ and start VS again.
The toolbox will display all the controls and will be appear as expected.

Creating an Empty file using SSIS Script Task

Add variable "FilePath" to the read variable in script editor
 public void Main()

{

// TODO: Add your code here



string filepath = Dts.Variables["FilePath"].Value.ToString();

           



System.IO.File.Create(filepath);
           

Dts.TaskResult = (int)ScriptResults.Success;

}

Flat file destination with Header and Detail rows

The expected output of the file is (Fixed header and details tab limited)

## Import File v:2.0      
## pre-process header.   
## D:2013-03-14 12:59:23        
Key Product Name          
71 Hollywood Flesh Latex         
108 The Wizard of Oz Wicked Witch Adult Costume         
204 Economy Ninja Sword         
214 Sabre Tooth Bracelet         
220 Monk's Cross         
322 Looney Tunes Sylvester the Cat Adult Costume 

To generate the output we have to use two flat file destination connections and both connection string should map to the same destination variable(use the variable).

Please follow the below steps to generate the file.

step 1. Create the dataflow task and use script component as source to generate header text.

use below script to generate the header rows

public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        Output0Buffer.AddRow();
        Output0Buffer.Header = "## Import File v:2.0 ";
        Output0Buffer.AddRow();
        Output0Buffer.Header = "## pre-process header";
        Output0Buffer.AddRow();
        Output0Buffer.Header = "## D:2013-03-14 12:59:23;

    }

Step 2 . add flat file destination to generate the output file and make sure to click override the data in the file check box

Step 3 . use oledb source connection to get the details rows.

Step 4. Add flat file destination to generate the output file with tab limited and make sure to uncheck overwrite data in the Flat file destination Editor  ( By unchecking the box will make sure the header rows are not overwrite)

Secure FTP using SSIS

For FTP we have FTP component in SSIS and for Secure FTP (SFTP) we have to use third party components. The third party component I would recommend is “SFTP” from codeflex. You can download the dll from below location and this work for SSIS 2008. It’s very simple to use.

http://ssissftp.codeplex.com/

After downloading the dll please follow below steps

1.       Copy the dll to the below locations

a.       “C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\” folder for 64bits operation systems

b.      “C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\” for 32bits operation systems

2.       Register the dll to GAC as below

a.       Gacutil can be find at the location “C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin”

Command to register  is “gacutil /uf "path of SSISSFTPTask100’dll" or

b.       Simply copy the dll to the folder "%WindowsDir%\assembly"