Help with understanding programming time/difficulty - SQL Data dump vs SSIS Packaged report I manage a team of data reporting analysts.  One of our main sources of data is run by another segment.  It is a SQL database, but they will not allow direct access to the data.  

We have 3 csv files that are run automatically every evening that provide us with the bulk of the data we need to run reports. Recently new fields were added to the database and we need access to the information in those fields.

The original plan was that they would add the new fields to our existing data dump files or provide us with an additional data dump file with the new information.

Today, I was informed that they do not have the capacity to create or modify the data dump files, but that they can provide SSIS reporting, allowing us to open the front end of the database and export the data.

I need to know if creating an SSIS Reporting package that is exportable from the front end of the database is truly easier and less time consuming to develop than modifying or adding a package that will dump the files into a folder.

The issue I have is that we are attempting to automate all of our work.  Direct access would be the easiest way to automate reporting,  csv files cause some issues with automation, but can be automated.  Having to open the front end database application and log in and then export the data to excel is crippling my ability to automate our processes.  

My experience is that one automation or another didn't really make a difference, but it has been a long time since I managed a data collection application.
I would love to hear from anyone who currently works with data collection systems and could provide me with the knowledge needed to either accept this solution or push back with facts and solid information on the differences in creating an automated report or an automated data dump.

The team creating this extract specifically said they have  the capacity to build an "SSIS" report package so the data can be exported from the front end of the database, but they do not have the capacity to build a new data dump with the new data or append the new data to the existing data dump. The new fields are all associated with the user ID.

They will not allow me access, but if I were creating a database with normalized tables this would be a straight dump of the user data joined to the supervisor table - providing historical supervisor effective and end dates. When you create a data dump or an extract executed from the front end of the database, would you use the same SSIS package - the difference being that one is scheduled and one is running on live data on demand?
I've been working with SQL Server for about 17 years. If I need to extract data on a repeating basis, I will almost always do it with an SSIS package.

At the risk of ticking off your SQL guys:


SSIS packages can be executed on a schedule (either using SQL Server jobs, or Windows scheduler if necessary). they are my first choice for automated data extraction.
SSIS can output data to CSV files, Excel files, or can even move it directly to other databases.
SSIS packages can be configurable, allowing changes to certain parameters without having to change the actual package.
For that matter, SSIS package files (*.dtsx) are actually XML files, allowing for certain changes to be made with a text editor (like, changing the name of a variable everywhere in the package); not that this is a feature that would be likely to help you, but....


Obviously, I can't tell you why adding a few additional fields to an existing export routine is outside their capacity right now. In most cases, I wouldn't expect adding a few additional fields to an existing extract process to be a major issue or to take much time. However, if (due to the nature of the extract process, or of the additional fields) a change to the format of the data were involved, or if the data required special handling (for example, a "notes" field with embedded tabs, carriage return, or newline characters), more time and effort might be required than you would expect.

It's possible that the problem is less the complexity of the change you need, than the amount of work already on their plate, and its priority level. If letting you get the data for yourself lets them stay on task, then it may be viewed as an acceptable compromise.

You mentioned that the extract you had been using came from the "front end" of the database. Generally, that's some sort of application (whether a compiled .exe, a script of some sort, or something web-based). Modifying an application does take time, and usually requires some degree of testing beyond just the immediate area of change (to make sure that none of the new code inadvertently changes anything outside that area, causing something to break).

If other development work on the application is under way, it can be difficult and time-consuming to bypass that to come out with a minor update to change a report. An SSIS package, being a standalone entity, is less likely to break anything else (as long as you aren't changing data), and thus can be developed and stood up without interrupting or interfering with other work.

NOTE: I'm interpreting the difference between a "data dump" and a "report" as being a matter of formatting. There are some things you can do to (exporting to a preformatted Excel template) that allow a certain amount of formatting, and some more complicated things (adding the appropriate HTML tags to your data to actually create an HTML file, instead of a CSV file or some such). However, it may often be easier (and faster, in the short- and even mid-term) to apply formatting after the fact.

Another NOTE: While this may have improved in later versions of SSIS, there are some issues when working with Excel files. These tend to be worst when importing data from them, but some of the issues (default limit of 255 characters per cell (unless one of the first eight rows has more than that), for example) do apply to exports. I've always tried to keep my life simpler by using .CSV files instead of actual .XLS files, for input or output.



For a simple one-off data extract, I would probably put a query together in SQL Server Management Studio, and copy the results to a spreadsheet of save them to a CSV file. For anything that will be repeating on a regular basis, I'll turn it into an SSIS package.  Either way, I'm hitting "live" data.

It's also possible to run an SSIS package from the command line (dtexec.exe), or with a simple, direct user interface (dtexecui.exe), rather than having to open it up in SQL Server Data Tools (or Business Intelligence Development Studio, depending on your SQL version). So, an SSIS package can be scheduled or "on demand". Whether the data is live or not depends on the database you're querying, not on SSIS itself.