How to use variable mapping while using Oracle OLE DB provider in SSIS?

It feels like you're mixing items. The Parameterization ? is a placeholder for a variable which, in an OLE DB Source component, you'd click on the Parameters button and map.

However, since you're using the SQL Command from a Variables, that doesn't allow you to use the Parameterization option, probably because the risk of a user changing the shape of the result set, via Expressions, is too high.

So, pick one - either "SQL Command" with proper parametetization or "SQL Command from Variable" where you add in your parameters in terrible string building fashion like Dynamically assign value to variable in SSIS SQL Server 2005/2008/2008R2 people, be aware that you are limited to 4k characters in a string variable that uses Expressions.

Based on the comment of "Basically what I am trying to do is use the results of a query from a SQL Server table, (ie ..account numbers) and pull records from Oracle reference the results from the SQL query"

There's two ways of going about this. With what you've currently developed, my above answer still stands. You are shredding the account numbers and using those as the filter in your query to Oracle. This will issue a query to Oracle for each account number you have. That may or may not be desirable.

The upside to this approach is that it will allow you to retrieve multiple rows. Assuming you are pulling Sales Order type of information, one account number likely has many sales order rows.

However, if you are working with something that has a zero to one mapping with the account numbers, like account level data, then you can simplify the approach you are taking. Move your SQL Server query to an OLE DB Source component within your data flow.

Then, what you are looking for is the Lookup Component. That allows you to enrich an existing row of data with additional data. Here you will specify a query like "SELECT AllTheColumnsICareAbout, AccountNumber FROM schema.Table ". Then you will map the AccountNumber from the OLE DB Source to the one in the Lookup Component and the click the checkmark next to all the columns you want to augment the existing row with.

I believe what you are asking is how to use SSIS to push data to Oracle OleDb provider.

I will assume that Oracle is the destination. The idea of using data destinations with variable columns is not supported out of the box. You should be able to use the SSIS API or other means, I take a simpler approach.

I recently set up a package to get all tables from a database and create dynamic CSV output. One file for each table. You could do something similar.

Switch out the streamwriter part with a section to 1. Create the table in destination. 2. Insert records into Oracle. I am not sure if you will need to do single inserts to Oracle. In another project that works in reverse, dynamic csv into SQL. SInce I work with SQL server, I load a datatable and use SQLBulkCopy class to use bulk loading which provides excellent performance.

    public void Main()
    {
        string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
        try
        {
            string TableName = Dts.Variables["User::CurrentTable"].Value.ToString();
            string FileDelimiter = ",";
            string TextQualifier = "\"";
            string FileExtension = ".csv";



            //USE ADO.NET Connection from SSIS Package to get data from table
            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["connection manager name"].AcquireConnection(Dts.Transaction) as SqlConnection);



            //Read data from table or view to data table
            string query = "Select * From [" + TableName + "]";
            SqlCommand cmd = new SqlCommand(query, myADONETConnection);
            //myADONETConnection.Open();
            DataTable d_table = new DataTable();
            d_table.Load(cmd.ExecuteReader());
            //myADONETConnection.Close();



            string FileFullPath = Dts.Variables["$Project::ExcelToCsvFolder"].Value.ToString() + "\\Output\\" + TableName + FileExtension;

            StreamWriter sw = null;
            sw = new StreamWriter(FileFullPath, false);

            // Write the Header Row to File
            int ColumnCount = d_table.Columns.Count;
            for (int ic = 0; ic < ColumnCount; ic++)
            {
                sw.Write(TextQualifier + d_table.Columns[ic] + TextQualifier);
                if (ic < ColumnCount - 1)
                {
                    sw.Write(FileDelimiter);
                }
            }
            sw.Write(sw.NewLine);

            // Write All Rows to the File
            foreach (DataRow dr in d_table.Rows)
            {
                for (int ir = 0; ir < ColumnCount; ir++)
                {
                    if (!Convert.IsDBNull(dr[ir]))
                    {
                        sw.Write(TextQualifier + dr[ir].ToString() + TextQualifier);
                    }
                    if (ir < ColumnCount - 1)
                    {
                        sw.Write(FileDelimiter);
                    }
                }
                sw.Write(sw.NewLine);

            }

            sw.Close();

            Dts.TaskResult = (int)ScriptResults.Success;
        }



        catch (Exception exception)
        {
            // Create Log File for Errors
            //using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" +
            //    "ErrorLog_" + datetime + ".log"))
            //{
            //    sw.WriteLine(exception.ToString());
            //}

            Dts.TaskResult = (int)ScriptResults.Failure;
            throw;
        }


        Dts.TaskResult = (int)ScriptResults.Success;