CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssissql-update
SteveAnselment
SteveAnselment

Reputation: 644

SQL Update Statement works in SSMS but not in SSIS

I have an update statement that works fine in the SQL Server Management Studio but does not function correctly when used inside an execute SQL task in SSIS. Would anyone have any knowledge about why it would not work there as well?

Code Follows:

UPDATE AU_DETAILS 
SET AU_DETAILS.GroupName = 
CASE WHEN AU_DETAILS.GroupName = '' THEN 'L&I'
WHEN AU_DETAILS.GroupName = ' %' THEN 'L&I'
ELSE AU_XREF.MAP_VALUE  END
FROM AU_XREF, AU_DETAILS
WHERE AU_DETAILS.AU = AU_XREF.AU

Upvotes: 1

Views: 2137

Answers (2)

billinkc
billinkc

Reputation: 61249

First problem is that you are not using ansi standard join syntax. Break yourself of this habit and write maintainable code.

You are also not specifying your schema. Again, a bad habit to break yourself of.

The query however, works just fine in SSIS as it does in SSMS.

Sample fiddle

The following package simulates what you have described as your problem set

enter image description here

The following Biml was used to generate that package.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!--  http://sqlfiddle.com/#!6/53b0c/1  -->
    <!-- http://stackoverflow.com/questions/21611485/sql-update-statement-works-in-ssms-but-not-in-ssis -->
    <Connections>
        <OleDbConnection Name="CM_OLEDB" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;"></OleDbConnection>
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="so_21611485">
            <Variables>
                <Variable DataType="String" Name="QueryDDL" EvaluateAsExpression="true">"
IF NOT EXISTS(SELECT * FROM sys.tables AS T WHERE T.name = 'AU_DETAILS')
BEGIN
    CREATE TABLE AU_DETAILS
    (
        GroupName varchar(10)
    ,   AU int
    );
END

IF NOT EXISTS(SELECT * FROM sys.tables AS T WHERE T.name = 'AU_DETAILS')
BEGIN
    CREATE TABLE AU_XREF
    (
        AU int
    ,   MAP_VALUE varchar(10)
    );
END;


INSERT INTO dbo.AU_DETAILS
    (GroupName, AU)
VALUES
    ('',0)
,   (' %',1)
,   ('This works',2);

INSERT INTO dbo.AU_XREF
    (AU, MAP_VALUE)
VALUES
    (0, 'A')
,   (1, 'B')
,   (2, 'C');"</Variable>
                <Variable DataType="String" Name="QueryUpdate" EvaluateAsExpression="true">"
UPDATE AU_DETAILS
SET
    AU_DETAILS.GroupName = 
        CASE 
            WHEN AU_DETAILS.GroupName = '' THEN 'L&amp;I'
            WHEN AU_DETAILS.GroupName = ' %' THEN 'L&amp;I'
            ELSE AU_XREF.MAP_VALUE
        END
FROM
    AU_XREF
,   AU_DETAILS
WHERE
    AU_DETAILS.AU = AU_XREF.AU"</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLEDB" Name="SQL Generate Schema">
                    <VariableInput VariableName="User.QueryDDL" />
                </ExecuteSQL>
                <ExecuteSQL ConnectionName="CM_OLEDB" Name="SQL Update works fine">
                    <VariableInput VariableName="User.QueryUpdate" />
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

I ran the package to completing then re-ran the first execute sql task to regenerate source data. You can see my original data and the corrected data in the following screen shot.

enter image description here

Upvotes: 2

DimaSUN
DimaSUN

Reputation: 921

I replaced query by this

UPDATE AD
SET AD.GroupName =
CASE WHEN AD.GroupName = '' THEN 'L&I'
WHEN AD.GroupName LIKE ' %' THEN 'L&I'
ELSE AX.MAP_VALUE  END

FROM <schema>.AU_XREF AX
JOIN <schema>.AU_DETAILS AD on AD.AU = AX.AU

try to denote explicit schema

also check if You have enough permissions

Upvotes: 2

Related Questions