Post reply

Execute sql task convert string to int

  • February 28, 2011 at 7:02 am

    #149980

    Hi,

    I am assigning a concatinated sql satement to a variable and assigning this as my source.

    I am doing something as below. In the code I am unable replace the User vaiable "Code" with integer. Could anyone please help as this is very urgent.

    var sbPkgGuid = new StringBuilder();

    sbPkgGuid.Append("\" if exists(SELECT ETL_Pkg_Reg_ID FROM mds.tmdrt_ETL_Pkg_Reg WHERE ETL_Pkg_GUID = ");

    sbPkgGuid.Append(" '\" + @[User::Pkg_GUID] + \"') ");

    sbPkgGuid.Append(" Begin ");

    sbPkgGuid.Append(" SELECT Pkg_Reg_ID FROM Pkg_Reg WHERE ETL_Pkg_GUID = ");

    sbPkgGuid.Append(" '\" + @[User::Pkg_GUID] + \"' ");

    sbPkgGuid.Append(" End ");

    sbPkgGuid.Append(" Else ");

    sbPkgGuid.Append(" Begin ");

    sbPkgGuid.Append(" INSERT INTO [BIP_MDS].[mds].[Pkg_Reg] (");

    sbPkgGuid.Append(" ID]");

    sbPkgGuid.Append(" VALUES (");

    sbPkgGuid.Append("'\"+ @[User::Code] + \"') ");

    sbPkgGuid.Append(" End ");

    sbPkgGuid.Append("\"");

    this.Variables["SqlLogQuery"].Expression = sbPkgGuid.ToString();

    this.Variables["SqlLogQuery"].EvaluateAsExpression = true;

    this.Variables["SqlLogQuery"].Value = this.Variables["User::SqlLogQuery"].Expression;

    sqlTaskGuid.SqlStatementSourceType = SqlStatementSourceType.Variable;

    sqlTaskGuid.SqlStatementSource = @"User::SqlLogQuery"; ;

    sqlTaskGuid.ResultSetType = ResultSetType.ResultSetType_SingleRow;

    sqlTaskGuid.BypassPrepare = false;

  • March 1, 2011 at 3:52 am

    #1292800

    First of all, this is an SSIS Script Task, not an Execute SQL Task.

    Regarding your error, you cannot reference SSIS variables like that.

    You need to use Dts.Variables("Code").Value to get the value of the @User::Code variable. Make sure you add the variable to the read variables in the script task editor.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply