February 23, 2021 at 11:55 pm
Dear friends,
Please some advice.
In my computer with SSMS, visual studio, I have a solution that includes a Data Flow Task with:
A OLEDB source with an stored procedure with RESULT SETS specified, this is a stored procedure inherited from a previous developer, this is organized as a temp table that load some data. A Flat file destination this will receive, the previous step data loaded and directed to a txt file.
Running in my computer it works perfect, do the job.
When I load in the Testing Server, and execute from the SSIS Catalog, the result is
No column information was returned by the SQL command
If I execute the store procedure directly on the SSMS of the testing server, works as expected, but running from SSIS Catalog as a package is not possible to run ok, when I run from my workstation all is working ok.
This is a very strange situation, please help
Thanks for your Ideas
February 24, 2021 at 11:32 am
Regarding this
A OLEDB source with an stored procedure with RESULT SETS specified
I just want to check. Is 'WITH RESULT SETS' within the stored proc itself, or do you have
EXEC PROC xyz WITH RESULT SETS
within your OLEDB source?
Also, does the version of SQL Server on the server match what is on the development machine?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2021 at 1:54 pm
Hi,
This is my invokation,
exec [dbo].[sp_MydearSP] Variable_1
WITH RESULT SETS
(
(
Par1 varchar(max) null,
Par2 varchar(max) null,
Par3 varchar(max) null
)
)
I'm using SQL Server 2016 developer edition, in the testing server we have SQL Server 2019 Developer Edition, in this server we only have the SSIS Server and catalog, so I call the package from this server, the source tables and databases are in a SQL Server 2017 standard edition.
Thanks
February 24, 2021 at 3:16 pm
I've used stored procedures as OLE sources but never included "WITH RESULT SETS" so I don't know what impact that has but the fact that it works in dev leads me to believe this is a security issue happening during validation.
When you say from the catalog do you mean you're right clicking and executing from the catalog or is this running as an agent job?
Does your account have access to the procedure on the 2017 server?
Does the job proxy have access to the procedure on the 2017 server?
February 24, 2021 at 3:44 pm
AlphaTangoWhiskey,
Yes, when I execute from the catalog is as you describe.
I open the ssms console using the account related to the Job and execute succesfully the same store procedure.
I run from the SSIS instance connecting using SSMS to the databases and run the store procedure and ok
I have configured the option RetainSameConnection to true, but without positive results.
Thanks
February 24, 2021 at 4:30 pm
I don't ever run packages from the catalog directly but I did create a simple ETL using a stored procedure for the source with the SET RESULT SETS clause and re-defined all the columns.
It worked fine in Visual Studio but failed when running from the catalog but it was because of Anonymous login.
I always thought it ran as the logged in user and I'm an admin on the SSIS server and the target server.
February 24, 2021 at 4:57 pm
The important clause is WITH RESULT SETS, not SET RESULT SETS, and if your data source stored proc is returning data from a temp table, its presence is mandatory, to the best of my knowledge.
Lou, can you take a look at the All Executions report and ideally post a screenshot of all the error messages?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2021 at 5:08 pm
EDIT: Sorry, I wrote that other post incorrectly nevermind, hey how do you delete a comment?
Phil, the SET RESULTS SETS is outside of the procedure and used to re-define the output of the stored procedure. I just learned that today as I have never used it, but is handy for sure. New feature in 2012.
The attached screen shot runs fine in VS. I don't see any issue with Lou's OLE Source query.
February 24, 2021 at 5:24 pm
Phil, the SET RESULTS SETS is outside of the procedure and used to re-define the output of the stored procedure. I just learned that today as I have never used it, but is handy for sure. New feature in 2012.
Even your own screen shot shows that the syntax is WITH RESULT SETS!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2021 at 5:46 pm
I know I know, lol after I posted that I realized what I did and couldn't figure out how to delete. Also in no way did I mean to imply you didn't understand how these things work. 🙂
This is what I get for trying to work on a real ETL while creating and deploying a test one to help answer an SSC post.
February 24, 2021 at 6:04 pm
If you look at the Overview page of the All Executions report, you will see at the top right a property called 'Caller'.
Can you confirm that the user mentioned here has rights to execute the stored proc?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2021 at 7:42 pm
Phil,
The caller is the same for another packages that are running ok, in the connection string it has a username and password with sysadmin privileges to connect to the database, in other packages this configuration works ok, and can execute the SP without problems from SSMS.
February 25, 2021 at 10:47 am
Phil,
The caller is the same for another packages that are running ok, in the connection string it has a username and password with sysadmin privileges to connect to the database, in other packages this configuration works ok, and can execute the SP without problems from SSMS.
Running out of ideas.
There must be one or more things about this proc which are different from those which are currently being executed successfully in SSIS. Eg, selects from temp table, different server, different database, different schema ... what can you think of which makes this proc unique?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply