SSIS SQL Executation Task error: unable to run some sql queries

Question

I'm working to make some fact tables (taking some data from some resources, doing some transformations and putting them in a table). My main dilemma is that I can't run any SQL query other than select, update, and insertion. As soon as i try:

exec someProcedure

or a conditional statement (if @part1 ...) or even (create table ...) I take errors. Opening the task to build my SQL statements and find problems it gives errors ranging from (The Set SQL construct or statement is not supported.) to (The EXEC SQL construct or statement is not supported.). I looked for numerous topics here on stackoverflow but none were actually addressing me problem.

Thanks,

You can see a view of what I'm facing in this picture :

I expect to run my SQL commands as usual in SSIS.

Answer 1

Try changing the SQL Source Type from Direct Input to Stored Procedure and just specify the stored procedure name instead of Exec stored procedure

Also make sure that you have selected the relevant TargetServerVersion from the project configuration:

• How to change TargetServerVersion of my SSIS Project

---

Based on your comments, you are using SQL Server 2012 with Visual Studio 2010 which are not compatible.

You have to use Visual Studio 2012 or 2015+ (backward compatibility added). You can refer to the SSIS tag wiki for more info:

• https://stackoverflow.com/tags/ssis/info