3

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 :

enter image description here

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
AliRa
  • 51
  • 6

1 Answers1

2

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:

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Unfortunately I cant find the alternative ( Stored Procedure instead of Direct Input) it seems the only options are file connection and variable – AliRa Jun 24 '19 at 06:52
  • @AliRa what are the sql server and visual studio versions? – Hadi Jun 24 '19 at 07:01
  • sql server: 2012, visual studio 2010 – AliRa Jun 24 '19 at 07:08
  • Then you should use visual studio 2012 since vs 2010 is not compatible – Hadi Jun 24 '19 at 07:15
  • I installed Sql server 2014 and visual studio 2015. no changes occurred... – AliRa Jun 29 '19 at 17:34
  • @AliRa Have you checked the TargerServerVersion property – Hadi Jun 29 '19 at 18:14
  • @AliRa check the first link i mentioned – Hadi Jun 29 '19 at 18:23
  • I checked that and it is already okay and fit with my sql server version, quite annoying that i cannot run my queries ... – AliRa Jun 29 '19 at 18:33
  • @AliRa what is the connectionstring you are using? Also check that user has needed permissions? – Hadi Jun 29 '19 at 19:36
  • I mean there isn't any option in accordance to what you said, but could you tell me where to find connectionstring ? in the meantime, I'm already running many of functionalities with ease. – AliRa Jun 30 '19 at 17:49
  • @AliRa can you provide a screenshot of the ole db connection manager configuration? – Hadi Jun 30 '19 at 17:57