I've created an SQL 2012 SSIS package from two different data sources which execute cleanly within the package. But, I need to execute from within an SSRS report. I understand that the SSIS package cannot be executed via an SQL account, and the package contains a parameter (so the SQL Agent is out), so I've had our network administrator set up a special Active Directory Domain account that's been given permissions to log on locally. Now I'm trying to set up the datasource within SSRS that can run the package and I'm not having any luck. I've tried setting up the datasource multiple ways, but I'm not having any luck. It needs to be a specific Windows account, not the passthrough of the user executing the report. Any Help would be greatly appreciated.
Have you considered making the report data come from a stored procedure and the stored procedure executes the SSIS package then returns the data you need?
Here is a link that outlines how to do something like that. It may be helpful:
why are you trying to do this? what is the requirement. what is the end game?
Yep, but from what I've read it's the same problem - you cannot execute the SSIS from a stored procedure that uses an SQL account and EXECUTE AS doesn't work. So, that's why I was trying to do it another way. Kinda seems weird that MS is making it so hard to execute a package from within SQL 2012. Wonder if this is different in 2016? We're in the process of upgrading.
I need to download and populate two tables from two different SQL servers but they datasets are so big that they need to process on the remote server instead of via a linked server or anything else. I've got it working within the SSIS. But, in order to execute via a stored procedure, I need to use a windows authenticated account. That's what I'm struggling with - getting the datasource logged in correctly via SSRS.
I still don't understand why you want to have the user run the SSIS from within SSRS, can you please explain?
To automate your SSIS package - you need to deploy the package to the Integration Services Catalog. Once deployed to the catalog - you can define the SQL credentials at the project level, the package level - or the agent job depending on how you created the data sources (project or package).
SSIS has always had the ability to setup and schedule agent jobs using SQL authentication. Prior to 2012 and the Integration Services Catalog - the methods available were a bit trickier to use. You could modify the package when saving it to the MSDB database so the username/password were stored security on the server - or you could utilize a package configuration stored in a table or file.
I've deployed the package to ISC. I can't use the scheduled agent job because I need to pass environment parameters. I need to be able to run via SSRS because it needs to be done at-will using different parameters for different outcomes, and this piece is only the first step. I need to process via SSRS after this step. But, whenever I tried to connect and run, I got the "must use a windows authenticated account" error. So, I scoured the forums and it appeared that someone got it to run using SSRS and a windows account. Our network administrator prefers specific account for things like this and established a windows account in active directory that has local log-on privileges. I've verified in SQL that the account has the rights to read, select, and execute for a test table and the SSIS package.
So, now I have a windows account and am trying to get the datasource within SSRS to connect and I'm having trouble just getting a good connection-before I even get back to the SSIS package. So, I backed off and am trying to isolate and go in steps. SSRS is configured to run and has been running great using a domain network account. In just trying to select from a table, using anything other than an SQL login, I get a login failed error. So, perhaps the first problem is getting SSRS to use a Windows authentication. I'm using Visual Studio and the deploying to the SSRS site. Any ideas?
I really think you are going about this the wrong way - SSRS should never be used as a scheduling system for creating data. You are building a process that can easily be broken by having 2 or more users trying to run the reports at the same time with different parameters.
To be able to help further I would need to understand much more about the process you are attempting - and why it HAS to be done through SSRS.
Hey Jeff,
Thanks for the reply.
We’re a healthcare facility and I have to determine eligibility to Medicaid for our provider reporting purposes.
The report will probably only be run a handful of times per year, but needs to be run using parameters for different 90-day date ranges.
Basically, I have two separate databases I need to pull data from to populate tables on a third server’s database. They’ve very large databases and take about 35 seconds to run locally but over 30 minutes to run if I try to do linked server queries.
Next, I need to be able to run a couple of queries to populate two datasets for an SSRS report.
I’ve got a time delay built into the SSRS call to the SSIS package to give it time to run before I do the final SSRS query.
I figured I could restrict the access to the SSRS report to one person to eliminate traffic/parameter conflicts.
Does this answer your question(s)? I’m open to suggestions.
Thanks,
Janet
Hello Janet,
I am also in Healthcare - I am assuming you are pulling eligibility/enrollment information from multiple PM systems. I am at a loss though as to why these extracts could not be setup to pull all of the relevant information you need for all time frames. A daily/weekly/monthly extract that updates local tables with this information would be my recommendation.
I am also confused by the idea that you need to then run queries to populate a dataset. A dataset is either a query (embedded in SSRS) or a stored procedure. If you utilize a stored procedure to generate the data for the report at the time the report runs - from the local tables you populate in the first step, then you would not have to worry about using a linked server or building other tables to populate a dataset.
Generally - my processes are:
- Extract data from source systems needed for the reports - automated using SSIS and SQL Server Agent.
- Create stored procedure to generate report data at report execution using whatever parameters the user selects
Just need to insure that we have the data available locally for any of the possible parameters the users would enter...and that the local tables have appropriate indexes to support the report - starting with an appropriate clustered index and adding non-clustered covering indexes as needed.
A final process could be added to purge old data from the local tables - but that would only be needed if there is great concern over how much space is being utilized for those local tables.