I'm trying to setup a semi automated system and I have a question or two about how I can automate some of the table generation and SSIS and reporting.
First some background. I'm going to be occasionally provided with (usually small datasets in CSV format. Each of the datasets will have a schema which I can use to generate the tables. Over time each dataset will have updates which I would like to add to the tables using as automatic a process as possible.
For each new Dataset I need to generate a table, attempt to import the data and send any errors to pre-existing tables. The rest of the application is written in .NET and uses T4 templating which basically means I don't need to write anything, I just run the T4 template and it generates all the code for me. I now want to do something similar on the SQL parts.
I can see how I can autogenerate a table based on the schema, what I can't see is how I can autogenerate any SSIS. So my question is, are there any options out there that I may be unaware of. If it makes a difference I am using SQL Server 2012 Standard edition but has BI edition available if needed
tia
steve