Execute sql task mapping variables in ssis

Question

INSERT INTO [DEV_BI].dbo.[DimAktivitet]([Beskrivning],[företag],[Projektnummer],[Aktivitet],
            loaddate)
SELECT NULL,
       a.DATAAREAID,
       a.PROJID,
       a.MID_ACTIVITYNUMBER,
       GETDATE()
FROM [?].dbo.[v_ProjCostTrans_ProjEmplTrans] a
LEFT OUTER JOIN [DEV_BI] .dbo.[DimAktivitet] b ON a.MID_ACTIVITYNUMBER = b.Aktivitet
AND a.DataAreaID = b.företag
AND a.ProjID = b.Projektnummer
WHERE b.Aktivitet_key IS NULL

I have this above sql code in execute sql task and in the parameter mapping i have mapped a variable named user::connectionstring with data type nvarchar , parameter name = 0. Im getting this following error.

[Execute SQL Task] Error: Executing the query "insert into [DEV_BI].dbo.[DimAktivitet]([Beskrivni..." failed with the following error: "Invalid object name '?.dbo.v_ProjCostTrans_ProjEmplTrans'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

please someone help me to solve this.

When you are mapping variables on an Execute SQL Task, it means that you want to use real variables on SQL too, you can't use it to change the database of a query from a variable like that. You should change the connection string from the "expressions" property — Lamak
could you please explain me in detail so i will try to implement it. — Rajkumar

Registered User Registered User · Accepted Answer · 2013-04-01T19:48:29

It appears you are trying to change the database based on a variable. The Execute SQL Task can only use parameters as filters in the WHERE clause. This behavior is described in this TechNet article. For instance, you could do this:

insert into [DEV_BI].dbo.[DimAktivitet]([Beskrivning],[företag],[Projektnummer],[Aktivitet],loaddate)  
select null,a.DATAAREAID,a.PROJID,a.MID_ACTIVITYNUMBER,GETDATE() from 
[DEV_BI].dbo.[v_ProjCostTrans_ProjEmplTrans] a 
left outer join 
[DEV_BI] .dbo.[DimAktivitet] b
on a.MID_ACTIVITYNUMBER = b.Aktivitet AND a.DataAreaID = b.företag AND a.ProjID = b.Projektnummer
where b.Aktivitet_key is null
AND b.SomeFilterCriteria = ?;

If you really want to vary the database based on a variable, then you have three options:

Vary the Connection Manager connection string to your database connection based on an expression as described in a blog post. This is the best solution if you are only changing the database and nothing else.
Generate the entire SQL code as a variable and execute a variable as the SQL command instead of passing variables to the Execute SQL Command. This is described in this blog post under the section "Passing in the SQL Statement from a Variable".
Create a stored procedure, pass the parameter to the stored procedure, and let it generate the SQL it needs on the fly.

Execute sql task mapping variables in ssis

1 Answers