January 10, 2023 at 10:39 pm
Hello,
I have written a SSIS package on the server which on processing data outputs a CSV file to a network folder.
The package needs to be used by end-user on windows client PC. with no SQL/SSDT tools.
One way of calling the DTSx, i thought of is WMIC. it works I know, but is there a better way of doing this?
Also, I am not sure how to send parameters into SSIS via a batch file called inside a WMIC call.
Thank you,
V
January 11, 2023 at 9:11 am
Assuming the package has been deployed to SSISDB, it may be called (with parameters as needed) from a stored procedure. Would that help?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2023 at 10:47 am
Hi Phil,
no, the SSIS is not deployed yet to SSISDB.
Also the client PCs/users will not have any SQL tools on there. I could install basic SQL client (free) on client PC, but it doesn't come with full DTEXEC functionality.
I thought WMIC will work, but that failed actually.
finally I ran it with a batch file calling dtexec on the server using UNC pathnames. that worked.
But could there be a better way? I will need to send a paramater to SSIS package too.
Haven't worked out yet, how to do that.
Thank you,
V
January 11, 2023 at 10:49 am
Assuming the package has been deployed to SSISDB, it may be called (with parameters as needed) from a stored procedure. Would that help?
likely not - SSIS catalog does not pass credentials from the invoker - so accessing a network share would fail with lack of permissions on 'NT AUTHORITY\ANONYMOUS LOGON'
to the OP - SSIS is a server side (licensed) bit of software - so any execution of the package needs to be done on where it is executed (and no using it on Visual Studio/SSDT is not a licensed way of using it for PRODUCTION uses).
why exactly did you do it using SSIS instead of using either just stored procs and/or c#/powershell?
and what exactly is that package doing?
there are ways of executing the package on the server but some of them are security risks (requires enabling a few things on both server and client), another one is using xp_cmdshell (which needs to be configured correctly to even be considered by any good DBA, and it also has potential issues in that the account used is global to the server - only an issue on shared servers).
others are more complex (I have a setup that allows users to kick off a SQL Agent job with then reads a table with the command line to execute on the server - its complex due to the potential of allowing multiple executions of the job by different users - meaning we need to "queue" the executions)
another option is to have a job continuously running on the server which reads a table with commands to execute - and each time you need to run the package you add new entry to that table with required parameters and the package gets executed - again semi-complex to setup.
January 11, 2023 at 11:21 am
Hi Fredrico,
The SSIS package calls a SP and outputs the CSV to a network folder. The end users need this CSV for further processing in some other CRM.
A couple of end users will be using this to get the CSV file, they need.
hence I packaged everything in SSIS. But now the problem is how are they going to run a batch file which has remote server unc pathnamed dtexec call.
I just tested this on a pc with no SQL tools and it fails.
Works for my pc, as i do have SQL SSDT tools installed.
How do I get them to execute this SSIS?
Vinay
January 11, 2023 at 12:00 pm
So all the SSIS package does it call a SP and generate the file? or does any type of transformation on the data it gets from the SP?
if no transformation at all then scrap that SSIS package and just create a small c# app (or a powershell script - likely easier) to execute the SP and generate the CSV file. extremely easy in powershell, a bit more work on C# in order to generate a proper CSV file, but not much harder.
if it does more than just call the SP then please be very specific on what it does so we can give alternatives.
January 11, 2023 at 12:07 pm
At the moment there is no Data transformation. This is work in progress. Unlikely i may need it, but can't be sure.
I have not done a powershell script before.
Does the user need to be added to the SQL Security to a database role for SP execute?
How would I convert the output of the SP to a CSv file in powershell?
V
January 11, 2023 at 12:22 pm
for anything being kicked off by the users against the server the user will require access to the server and to the database - access can be restricted to exactly what he needs to execute but access will be required for sure. - if nothing else at lease execute permission on the SP will be required. (whatever you do make sure you use a AD group to grant permissions, never to the individual user specifically)
to use powershell - see one example here - https://community.spiceworks.com/topic/2283593-need-to-export-a-stored-sql-procedure-to-csv
there are other ways - that is just one.
one possible alternative to allow users access to the database is to create a small web site and allow users to enter parameters for the execution of the code - and then execute the same powershell/c# code to generate the file - that way users don't need access to database.
January 11, 2023 at 9:35 pm
Another option is to use SSRS instead of SSIS - which then gives the end users a portal where they can access the 'report' and export it whenever and however they need it for additional work.
A very simplified report with just a single tablix - column headers and detail row for each column. Add in the possibility of setting up snapshots, history and subscriptions and you now have many options to provide the end users.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 12, 2023 at 9:52 am
Fredrico,
powershell is so restricted and gives me all kind of errors. Scripting is disabled on group policy.
I think I will try python route to access the SP and convert the output to a CSV.
Many thanks for your help.
V
January 12, 2023 at 9:54 am
Hi Jeffrey,
yes, SSRS is a brilliant idea. not sure why it didn't occur to me. I had created few SSRS reports in past.
I will consider SSRS or a python script.
Thank you so much,
V
January 12, 2023 at 10:56 am
If the package is on a server and the person has SSMS installed, couldn't you just create a SQL Server Agent Job that runs the package and give the person permissions to run the job?
You could then use a config file to determine where to output the csv, etc.
January 12, 2023 at 11:40 am
Mark,
The client PCs have no SQL tools at all. I did consider to install just the free SSMS client. But I was refused that request.
May have to go back to them to re-consider. Otherwise I will use SSRS.
Thank you
V
January 12, 2023 at 12:42 pm
Ah okay.
Another option: a scheduled task that runs on SQL Server agent and checks for a file existing on a shared area (users put the config file there).
It takes the items within the config file, runs a package, then deletes the config file. You could even build a ForEach loop in your package if there's more than one file to process.
DB mail is another option, if they'll let you set it up!
January 12, 2023 at 2:25 pm
Hi Jeffrey,
yes, SSRS is a brilliant idea. not sure why it didn't occur to me. I had created few SSRS reports in past.
I will consider SSRS or a python script.
Thank you so much,
V
Thinking a bit more on the SSRS idea. Remember that SSRS can call a stored proc as its source. The stored proc that it calls can do more than just return a recordset ... it could call the SSIS package too (once you have deployed it to SSISDB). You could use SSRS parameters to capture the parameters to be passed to the SSIS package.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply