Automating jobs to load into a database

N2IT

Anyone have any good books or websites to visit to learn more about this. I am looking to build a solution that runs every hour goes out to network share picks up files and loads them into our database. Right now I am just looking to research more than anything. Thanks in advanced

NovaHax

Any thought as to what scripting language and/or database you are looking to use?

NotHackingYou

You could use SQL Agent jobs to do this or powershell. I have also used a product named Automate BPA server that is decent.

N2IT

Oh yeah we have a T-SQL environment running SSMS 2012 on 2012 OS platform. I'm not sure what scripting language would be the best to reach those files. I am reading up more and more on Stored Procedures, they might be able to do it. I just don't know enough yet.

Thanks for the follow up.

Carl - I am looking at SQL Agent jobs currently. Not sure yet though.

NotHackingYou

I generally try to stay away from working in the file system from SQL calls if at all possible. However, you could powershell script your file system pieces and call the powershell from SQL.

Also take a look at sql server extended events.

Akaricloud

What we do with a couple custom pieces of software we wrote to monitor a FTP landing zone for new files(and their stability over time), pulls them down to local servers and parse them into standardized CSV format. From here we're able to create links from our data warehouse appliance to these files to treat them as external tables that get inserted onto the end of our main internal tables.

In reality we've further complicated this across 30 servers with tracking tables, error directories, file archives and grouping small files together into batches.

How in depth and how robust of a process you intend to build will likely depend on your file volume as well as importance of this data.