Parameter substitution in an Execute Sql Task

 

hello,

 

i'm having problem parameter subsitution in "execute sql task".  i'm suspecting problem related fact that the "execute sql task" uses cross apply, , perhaps have sort of syntax problem. 

i suspect because other "execute sql task" components have don't have problem, , "execute sql task" components use cross apply have problem.  i'm hoping syntax mistake me, , not ssis limitation.

any appreciated.

here's basic situation:

• i have c# program passes guid ssis package, shown in simplified form here:

guid _importjobid = guid.newguid();

package dtspkg    = app.loadpackage( packagepath, null );

variables pkgargs = dtspkg.variables;

// note: have convert string since ssis doesn't list guid supported type when defining package-level variables.

pkgargs["importjobidentity"].value = _importjobid.tostring();

dtsexecresult result = dtspkg.execute();

if (dtsexecresult.failure == result)

{

errors logged here.

}

 

• in nutshell, high-level logic here c# code drives ssis package, , c# code wants tell ssis package apply logic set of rows (in tables used package) have particular value particular column.  idea have "batch id" (called "importjobid" in our application) want group rows processing.

• note i'm not sure whether or not should prepend importjobidentity with user:: in c# code above, issue shouldn't have issue i'm discussing in post.   i'll deal issue seperately.

• in corresponding ssis package, define package-level (user) variable same name (importjobidentity) indicated in c# fragement above.  define have "package" scope , data type of "string".  specified "string" since there no option in drop-down list "guid".   explains why call tostring() on _importjobid when assign _importjobid to                                               pkgargs["importjobidentity"].value.

• in "parameter mapping" tab of "execute sql task" (see next bullet details of "execute sql task"), choose package-level variable, choose "input" direction, choose "guid" data type (since there no "string" type offered in data type drop-down), , specify parameter name 0 (which the naming convention required since connection manager ole db).

• here sql defined in "execute sql task".  highlight problem line in red:

        update      ztemp_520100_financialtransactions
        set         visitarid     = a.visitarid,
                    iarmaileddate = a.maileddate
        from  ztemp_520100_financialtransactions ztft  cross apply
        (
            select top 1 viar.visitarid, viar.maileddate
            from  visitar viar 
               inner join visits v 
                  on viar.siteid  = v.siteid    ,
                     viar.visitid = v.visitid
                        inner join visitpayers vp
                           on viar.siteid = vp.siteid   and
                              viar.visitpayerid = vp.visitpayerid
            where    viar.siteid = ztft.siteid        and
                     v.accountid = ztft.accountid     and
                     vp.plancode = ztft.plancode      and
                     viar.visitarstatusid not in (1,2,3) 
            order by
                viar.cobsequence, viar.visitarid -- use visitarid tiebreaker
       ) a
       where    importjobid = ?

logically, i'm trying do, the line  where importjobid = ?, filter things rows

of  ztemp_520100_financialtransactions whose column importjobid matches value of package-level variable importjobidentity affected sql.

 

when run c# code above,  dtspkg.execute() returns dtsexecresult.failure and i get info in log, regarding error:

failed following error: "syntax error, permission violation, or other nonspecific error".
possible failure reasons: problems query, "resultset" property not set correctly, parameters not set correctly,
or connection not established correctly.

when run sql directly in sql server client gui, error:

incorrect syntax near '?'.

 

i have other "exectute sql task" components that specify parameter mapping describe above component error, , use of '?' similar.  example, here guts of 1 such "execute sql task" seems work:

 

update      ztemp_520100_financialtransactions
set             accountbalance     = a.accountbalance,
                accountstatusid    = a.accountstatusid,
                collectionstatusid = a.collectionstatusid
from  ztemp_520100_financialtransactions ft

          inner join accounts
               on  ft.siteid    = a.siteid       and 
                   ft.accountid = a.accountid
where    importjobid = ?

 

again, any appreciated.  if need more explanation, feel free call me @ 214 382 3514 or contact me @ email address:  jrobottom@xactimed.com.

 

thanks!

rafael,

 

i noticed in blog that you mentioned, in response not having expression builder, that the expression builder variable added in service pack 1. should install latest sp available.

 

by "service pack" mean service pack visual studio (aka bids), or sql server, or windows, or what?  i'm not knowledgeable windows stuff, i'm not sure if there service packs particular applications (eg, visual studio).

 

thanks

 

SQL Server  >  SQL Server Integration Services