tdmitch

USE testdb
GO

CREATE TABLE invoices (
invoiceid INT IDENTITY ( 1 , 1 ),
vendorid INT,
invoicedate DATETIME,
invoiceamount DECIMAL(10,2))

INSERT invoices

tdmitch

public override void CreateNewOutputRows()
    {
        // Create a connection to the file
        StreamReader reader = new StreamReader(Connections.SalesFile.ConnectionString);

        // Skip header row
        reader.ReadLine();

        while (!reader.EndOfStream)
        {

tdmitch

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion

// Add in the appropriate namespaces
using System.Data;
using System.Data.OleDb;

tdmitch

// The following is used to set up a DataTable object that will store the list of files
//  retrieved from the FTP server.
DataTable dt = new DataTable();
dt.Columns.Add("FileName", typeof(String));
dt.Columns.Add("ModifiedDate", typeof(DateTime));

tdmitch

// Setup session options
SessionOptions sessionOptions = new SessionOptions
{
    Protocol = Protocol.Sftp,       // Can be SFTP, FTPS, or plain FTP
    HostName = Dts.Variables["$Package::pHostName"].Value.ToString(),
    UserName = Dts.Variables["$Package::pUserName"].Value.ToString(),
    Password = Dts.Variables["$Package::pPassword"].GetSensitiveValue().ToString(),
    GiveUpSecurityAndAcceptAnySshHostKey = true
};

tdmitch

foreach (RemoteFileInfo fileInfo in directory.Files)
{
    if (!fileInfo.IsDirectory)  // Skip subdirectories
    {
        // For each file found, add a new row to the DataTable object created above
        DataRow dr = dt.NewRow();
        dr["FileName"] = fileInfo.FullName;
        dr["ModifiedDate"] = fileInfo.LastWriteTime;
        dt.Rows.Add(dr);
    }

tdmitch

// After all of the files have been added to the DataTable object, set the SSIS variable vFileList
//  to the value of that DataTable object.
Dts.Variables["vFileList"].Value = dt;

tdmitch

public override void CreateNewOutputRows()
{
    // Convert the generic SSIS object variable named vFileList into a DataTable
    DataTable dt = (DataTable)Variables.vFileList;

    // Iterate through each row in this DataTable object, writing each to a new row in the Output
    foreach (DataRow row in dt.Rows)
    {
        // Add the new row
        FileListBuffer.AddRow();

tdmitch

// This try/catch block will capture catastrophic failures (such as specifying the wrong path to winscp).
try
{
    winscp.Start();

    winscp.StandardInput.WriteLine(sessionOptionString);
    winscp.StandardInput.WriteLine(connectString);
    winscp.StandardInput.WriteLine(getString);
    winscp.StandardInput.Close();

tdmitch

<!-- Create controller package -->
<Package Name="Create Tables - Master" ConstraintMode="Linear">    
    <Tasks>
    <# 
        /* Loop through the tables, create one ExPkg task per table */
        foreach (var table in importResult.TableNodes) { 
    #>  
        <ExecutePackage Name="ExPkg <#= table.Name #>">
            <ExternalProjectPackage Package="Create <#= table.Name #>.dtsx" />
        </ExecutePackage>