tdmitch

-- Set up the execution instance
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
	@package_name = N'30 Parameterized Package.dtsx'
	, @execution_id = @execution_id OUTPUT
	, @folder_name = N'Making the Most of the SSIS Catalog'
	, @project_name = N'Making the Most of the Catalog'
	, @use32bitruntime = False
	, @reference_id = 2

tdmitch

SELECT Cast('char value' AS CHAR(20)) [val1] 
INTO   test1

SELECT Cast('char value' AS CHAR(40)) [val2] 
INTO   test2

SELECT t1.val1, 
       t2.val2 
FROM   test1 t1 
       INNER JOIN test2 t2 

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();