Execute SSIS Package

SSIS and Import/Export
1

Is there an way to execute SSIS Packages by non-technical users? I have created an import package to import into the database a table of account balances from another system. After the import, a report is run that queries against this newly created table.

2

yes but I would not recommend it. What is your end game here, what are you trying to accomplish?

  1. Why is the process creating a new table everytime?
  2. What you can do is have users do something else that triggers the SQL job to run such as them dropping off a certain file in a shared folder in the network.
3

The process creates a new table everytime because every day data changes. The users (well basically me as of now) is doing the following:

  1.   Running a query off of an SIS system (that we cannot integrate with SQL Server).

  2.   Saving the results from the query as a CSV file

  3.   Executing the SSIS package within SQL Server Tools

  4.   Telling the Users that they can run the report where this data is combined into.

Basically, we are a higher education financial aid team and we are using a different system from the Student Information System to Award Financial Aid. The SIS is housed by an external partner. WE are not able to get a direct connect to their database where the SIS is housed so that is not an option at all. So I am running a query that they wrote to get student account balances. Then Iā€™m combining that with a report I used to get anticipated aid for a student and then showing what their true balance would be.

4

How about creating a SQL Job that is scheduled to run every 30min. Then all you will need to do is drop the file into a shared folder and the SSIS package will pick it up and ingest it?

The ultimate use of SSIS is to automate things. If in the whole pipeline you are doing things manually it defeats the purpose.
Any chance the SIS owners can create some automation that creates the csv file for you and dumps to your systems as as ftp location?

1 Like