Developing dynamic packages using Microsoft SSIS

The objective of this SSIS package is to export database table into a flat file as per user input. The complete solution of the package will look like this;

Our first step will be to declare two variables in our package. Name it as ‘TableName’ and ‘ValidateName’.  We will use these variables to store input value from the user and to validate the user input.

Drag and drop ‘Script Task’ into control flow  from SSIS toolbox

Asign ‘User::TableName’ value to ReadWriteVariable

Now click on Edit Script…

 

This is where we will write our c# script. The below script when executed will display a input box for the user to provide the table name.

Now add a Execute SQL Task  to the control flow. This task will check if the input table provided by the user is present in the DB.

In the Execute SQL Task editer  add Variable Name to parameter Mapping as show below

And in the General tab provide the connection type and the connection and write the  SQL Statement.

Add a script task to the control flow  as show below and configure the precedence  constraint.

In the script task write the below  c# code to display a message for table not found in DB

string TableName;

        String Val;

              public void Main()

              {

            Val = Dts.Variables["User::ValidateName"].Value.ToString();

                     // TODO: Add your code here

            if (Val == "0")

            {

                TableName = Dts.Variables["User::TableName"].Value.ToString();

                Microsoft.VisualBasic.Interaction.MsgBox("The Table " + TableName + " is not present in the Database", Microsoft.VisualBasic.MsgBoxStyle.OkOnly,"Info");

                Dts.TaskResult = (int)ScriptResults.Success;

            }

                    

              }

Add Execute SQL Task to the control flow. This task will be used to BCP the table data to a flat file. Configure the Presedence constrain as shown below.

Now  edit the  Execute SQL Task  and provide the coneectionType and connection and write the below SQL statement. In the Parameter Mapping provide ‘User:: TableName’ as variable name.

DECLARE @String NVARCHAR(2000)

DECLARE @Path NVARCHAR(2000)

DECLARE @TableName NVARCHAR(200)

SET @TableName=?

SET @Path='F:\SSIS'

SET @String='BCP AucklandTransport.dbo.'+@TableName+' OUT '+ @Path+'\'+@TableName+'.txt -c -T'

print @String

EXEC xp_cmdshell @string

Add a script task to the control flow  as show below  and pass the ‘User::TableName’ as ReadOnlyVariables.

Write the below C# code in the Edit Script…

string TableName;

public void Main()

{

 TableName=Dts.Variables["User::TableName"].Value.ToString();

 Microsoft.VisualBasic.Interaction.MsgBox("The Table " + TableName + " has been   exported.", Microsoft.VisualBasic.MsgBoxStyle.OkOnly, "Info");

 Dts.TaskResult = (int)ScriptResults.Success;

}

The development activity is complete. Now Build the solution and execute the Package and you should be able to see the below results.