How to change the SqlStatementSource in a SSIS package through job step advanced tab

Question

I have a ssis package deployed and created a sql agent job which executes the package.I need to change the SqlStatementSource in one of the sql task in package through job step advanced tab. Can any one help me how to do that? I somewhere read its possible but not able to recall how exactly it can be done?

Answer 1

You have two choices based upon the source provider for changing your query.

DFT Test uses an OLE DB Source and DFT T2 is an ADO.NET Source.

My Data Flow is a Source to a Script Task.

The Source is a simple in-line query: SELECT 1 AS Foo;

The Script Task simply fires an OnInformation event so I can see the data row(s) as they flow through

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        bool fireAgain = false;
        ComponentMetaData.FireInformation(0, "Foo value", Row.Foo.ToString(), string.Empty, 0, ref fireAgain);
    }
}

OLE DB Source

This is going to require that you have done your work ahead of time. You can control the source statement through an SSIS Variable of type String. Here I chose to name it @[User::QuerySource] and have set the OLE DB Source to use a Variable as the query source.

That you can configure at run-time/SQL Agent

DTEXEC /file so_31100091.dtsx /set "\Package.Variables[User::QuerySource].Properties[Value]";"SELECT 2 AS Foo" /REP I

The above command would run the package and assign the value of SELECT 2 AS Foo to the Variable QuerySource, which lives at the root of the package. Finally, I have the engine report the Information events so it gets logged.

ADO NET Source

This is one of the few times, an ADO NET Source can be helpful. It can be configured directly without modifications to the package itself

DTEXEC /file so_31100091.dtsx /set "\Package\DFT T2.Properties[[ADO_SRC tempdb].[SqlCommand]]";"SELECT 3 AS Foo" /REP I

Here I use the command SELECT 3 AS Foo and then set task, "DFT T2" which has a source called "ADO_SRC tempdb", property of SqlCommand.

Wrapup

The important thing to note is the query provided must match the signature (column names and data types).

In the above I have manually executed the SSIS package. In the SQL Agent Job Step Editor, you will use the "Set values" tab to access the key value pairs.