0

I have an Execute SQL Task using this expression:

@[User::SQLExportError] + (DT_WSTR,30)@[User::Id]

The expression evaluates correctly to:

  select count(*) as ErrorTableCount from [myschema].Error 
    where SPName in ('export/INSERT','export/DELETE') and Id = 1000

In this same sql task I want to capture the as ErrorTableCount into a single row result set with the ResultName called: ErrorTableCount Variable Name called: User::ErrorTableCount

I am getting the red circle/x cross telling me: 

There were errors during task validation.

There seems to be some conflict between using an expression for the sql source and within that expression containing the the dubbed count name defined under the result set. Any advice/ideas?

plditallo
  • 701
  • 2
  • 13
  • 31
  • @Siva, it is a variable called SQLExportError concatenated with the Id variable. – plditallo Oct 31 '12 at 21:34
  • You should verify the Connection test if it is ok configure the "Result Set" to "Single row", set the variable in the "Result Set" page, put the expression in the SQLStatementSource and if that's still not working, we need more information to help you. :) – Rednaxel Oct 31 '12 at 22:40

1 Answers1

7

I am unable to pinpoint what is causing the error in your scenario. I have tried to recreate the scenario based on the question. The example might help you to figure out what is different on your end.

Create a simple table named dbo.Error

CREATE TABLE [dbo].[Error](
    [Id] [int] NOT NULL,
    [SPName] [varchar](20) NOT NULL
) ON [PRIMARY]
GO

Populate the table with some sample data using the below script.

INSERT INTO dbo.Error (Id, SPName) VALUES 
    (1000, 'export/INSERT'),
    (1000, 'export/DELETE'),
    (1001, 'export/INSERT');
GO

On the SSIS package, create a connection manager or data source to connect to the database where the above mentioned table is hosted. In this example, I have created an OLE DB connection manager, because that is the recommended provider for SQL Server databases and named the connection manager as Practice.

Create the following variables on the package.

Variables

Set the variable Id to the value 1000 so that the query we will execute in the Execute SQL Task will fetch at least two rows.

Set the variable SQLExportError to the following query:

SELECT COUNT(Id) AS ErrorTableCount FROM dbo.Error WHERE SPName in ('export/INSERT','export/DELETE') AND Id =

Select the variable SQLFetch and press F4 to view the properties. Set the property EvaluateAsExpression to True. Click on the Ellipsis button against the Expression property and set the following expression.

@[User::SQLExportError] + (DT_WSTR,30)@[User::Id]

Expression

Drag and drop an Execute SQL Task onto the control flow tab. Configure the General page of the Execute SQL task as shown below to execute the SQL query stored in the expression variable.

Execute SQL Task - General

Configure the Result Set page to accept the value returned by the query and store it in the variable.

Execute SQL Task - Result Set

Add a script task to view the value stored in the variable. Configure the script task to read the variable ErrorTableCount

Script Task

Add the following C# code to the script task.

public void Main()
{
    MessageBox.Show(String.Format("Value in variable ErrorTableCount: {0}", 
        Dts.Variables["User::ErrorTableCount"].Value), 
        "Execute SQL Task - Single Row");
    Dts.TaskResult = (int)ScriptResults.Success;
} =

Script Task Code

Close the script task editor. Control flow would look something like this.

Control flow

When you execute the package, the message box should appear with the count 2 because that is the number of rows in the sample data that have the Id set to the value 1000.

Hope that helps you to find what is wrong with your configuration.

Package execution

  • Siva, Thank you so much for all the time you took. The key was SQLFetch and setting the Source variable accordingly. Although I had produced all the steps @Rednaxel mentioned, that second variable was needed to avoid the validation conflict and allow this flow to work. Thank you again. – plditallo Nov 01 '12 at 13:14