1

Business Departments at our Finance company want IT to import various spreadsheets (companyid, productid, balance, transactions) into the databases.

Lets say, the SQL physical server harddrive contains G:\ [SQLFinance].

When business departments want to drop excel or flat files,

(1) Should we have them place excel/flat files in G:\ SQL harddrive or,

(2) Should we have them place files Not in SQL harddrives, but in another share drive Outside the SQL Server box? Then we copy files over with Powershell/Commandprompt/Robocopy (whatever) into our SQL Server harddrives? From there, we can apply SSIS to transform the excel-flat files into the sql tables.

Are there any benefits/disadvantages to each approach?

1

Your proposed project sounds fairly decent. However, the more hands involved the greater complexity security is.

If this was my request, I would ask some of these questions:

  1. Is the data stored currently on multiple data centers or centrally?

  2. Are these sources in the same domain? Or do we need multiple SSL’s for our ETL?

  3. If we choose to rely on external sources to transport this data to a local/network drive, how secure and consistent is this system?

  4. Can the system of question 3 be integrated into SQL Server or another software to copy or pull the data?

Why Network drives are best

For one, durability of the directories is independent of a SAN, disk, or single blade, so the data remains available even during outages.

Because they are on a single mount point, where each drive is additionally mounted, your security is simplified dramatically. You could give the files to SQL Server by using a single account that only SSIS has privy to, or a Domain Account that your AD manages.

But really, you should have an ETL process developed

  • You ensure the validity and security of your documents as you go from SOURCE to TARGET.
  • the process is encapsulated away so that additional sources is a mere plug ‘n play.

Proper ETL involves 3 stages:

  1. Extraction (your primary concern)
    • this involved programmatic pulling and staging of your source data.
    • Unless it is absolutely impossible, this process should use as little manual effort as possible.
    • If you find yourself with a lot of manual effort in this stage, often the data source may not be clean enough for your purposes.
  2. Transformation (inside the database/SSIS package)

    • This involves all processes that clean and ready the data for loading onto the final target tables.
    • cleanup usually follows this: Removing immediately bad data rows. Separating multifaceted values into their own column. Performing transformation steps that changes these values to a pre-final step. Last combinations of said columns that create the final tables OR allow your final loading to be collections of these values in a organized fashion (such as collecting from each sporting Team website details of their teams/history and then aggregating this in a final table)
  3. Loading. The final, cleaned, and final form for your data.

0

We have both the settings in my system. When we get files from external network shared through FTP downloaded to local drive and packages are run from there.

In internal network files are directly copied to a shared storage and SSIS is running packages from there. Few things to be noted here, access error, make sure you are running SQL services with domain account to get access on those shared folder.

Directly copying to a local drive reduces dependency of one more scheduled job. I hope this answer will help you, Thanks!

0

If your G: drive that resides in the SQL Server box is a dedicated shared drive, then I see little inconvenience in having them put the files directly on the server.

The main issue would be if your users are placing any other kind of files on that drive, potentially exposing it to malicious scripts.

On the other hand, if they use a shared folder on a file server, you have a bit more control over what files make it to your SQL Server, as you will only import/copy the relevant files.

Not a real answer, just some thoughts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy