Optimize architecture for SQL Server projects I often find myself hacking together multiple .sql files for a project and calling them one after the other through Task Scheduler. Everything I know about SQL is self-taught, so I don't know anything about useful shortcuts and best practices. I imagine there has to be a more elegant way to do what I do.
Example
Right now I work on a project in which I map GPS data from my camera photos to the GPS data of some addresses which are stored on a different SQL Server:

Import all new Address-Strings from a different SQL Server on the network (insert into..select from)
Fix the spelling of those strings (stored procedure with 50+ SET @str = REPLACE(@str, yada, yada))
call wget http://maps.googleapis.com/maps/api/geocode/json?address= [adress string] with xp_cmdshell and store the result in a text file
read it with SELECT BulkColumn FROM OPENROWSET(BULK N'D:\resultfile.txt', SINGLE_CLOB) AS Contents) to later extract Latitude and Longitude and store them in the address table
run exiftools.exe against new photos to get their GPS data too, store results in csv, BULK INSERT this CSV file into the pictures table
map pictures to addresses
create directories and move photos with xp_cmdshell

These actions are all accompanied by many layers of if xyz is not null BEGIN END&quot;, REPLACE(xyz, CHAR(10), '') and so on.
I also have three views, which are used by xampp/mysql on a local website to

show all address-photo-mappings in a tree-structure, including links to those photos
show all photos which were taken without GPS data or couldn't be mapped to a known address
show all addresses which couldn't be resolved by Google

All in all, I have:

6 .sql files
6 .cmd files which all do sqlcmd -S server\instance -i yada.sql
a scheduled task which calls these 6 .cmd files
3 views

Every little change I want to introduce makes me

re-check nearly all of these .sql files for compatibility
disable scheduled task while testing changes, reenable afterwards
alter view as.. because I can't just sync views via github

It is very tedious. Do you have any tips or something I should Google? I don't mind reading a bit on my own. Thanks!
You could create a script outside SQL Server (Powershell, VBScript, console app) which does away with your text files. The script would read input data from SQL, call the external utility and write the result directly back to the database.

If it was something written in C# then you have all the handy source control, automated tests stuff. Unfortunately, using SQL to perform external processes is possible, but painful. Sometimes you just need to chuck it in and write some middleware (in the middle of SQL and the external tools) to do the job for you.

For example, I wrote a VBScript that extracts a list of all tables from the DB then searches through .dtsx files to find them, then writes the result directly back to the database.

You may also be able to use SSIS and SQL Agent to simplify your process. SSIS is good for defining orchestration and data transfer in a visual way, though for more complex external things you often need to resort to C# script tasks. SQL Agent can be used to run SQL and command lines in order with basic workflow etc.



Community Wiki answer created from question comments by Nick McDermaid