I’m new to this job and don’t know all the ins and outs yet, but somebody queries the client data base and pastes the results into a spreadsheet. This has error rows for each client that has an error for that run. The rows for a particular client have to be emailed to that client. If there just happen to be a few clients then it’s easy and the user group splits the spreadsheet into separate clients manually. But a couple of weeks ago the query yielded thousands of rows representing hundreds of clients. It was a day long tedious effort for them to do this manually. The manager of the user group cast about for ways to automate the process. My manager suggested me, as I know (on an intermediate level) SSIS. The best thing I could think of was to being the spreadsheet into a SQL Server table, create another table of DISTINCT clients, cursor through that table and make and populate a table for each Client, Client1, Ciient2…Client150
All of this works. I have about 150 tables. My plan was, back in SSIS, to have a FOR EACH SMO loop to go through each table and make Client1.xlsx, Client1.xlsx, etc, But for some reason my FOR EACH SMO Enumerator is hosed and I don’t get the window I need to accomplish that. So I am looking for alternate ways as my IT team doesn’t know a lot about the ins and outs of SQL Server (they know how to install it from an installation kit). So I can’t count on them. I’ve already tried with the SMO problem and they don’t have any ideas other than to reinstall SQL Server (when they originally installed it , it had a lot of templates, but not the DSDT ones).
Last week it happened again. I created my 150 tables in my SQL Server Data Base, but I ended up in the Query Editor, selecting from each table and copying and pasting the data into Excel. I don’t plan to do this again. So I am looking to either:
1 – Get SSIS straightened out
2 – Bypass SSIS and make each table into a CSV or XLSX file from within SQL Server.
Does that give an idea of what my dilemma is?