Hi,
I have a stored procedure which creates a lot of tables (hundreds). I would like to make each table into a CSV of XLSX file (for some reason the FOR EACH SMO Enumerator is hosed), so I am trying to find a way (or the best/easiest way to do it from within my stored proc. My DBAs don't really know that much about SSIS, xp_cmdshell, etc (nor do I).
What are your suggestions about how to accomplish this?
Thanks,
Dick
the methods you mentioned are the way to go: ssis, xp_cmdshell, powershell etc
so the bigger question for me is why are you doing this extraction, what is the end game?
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?
Thanks,
Dick
OK I understand. so who is who here? dr2706/Richard_Rosenberg . is this the same person so I Know which thread we are replying to?
Anyways, is there any possibility you could install SSRS but that seems maybe for another day.
ok so what you can do is create a sql job that does not involve any SSIS and you could leverage xp_cmdshell which looks like it is already being used.
You can then use a stored procedure that you can call from within the sql job and create one file for each customer.
Unless you want to continue the manual way which seems like you do not want to?
Please post some sample tables we can use with some sample data?
I would be very reluctant to do that (assuming they are physical, permanent, tables - albeit that you might drop them after the job is done).
I have negligible knowledge of SSIS but the thoughts that occur to me are:
A loop based on DISTINCT Clients [who have errors] which then does a sub-report/export based on that client
Failing that some code which delivers "Errors Report for the NEXT Client" using a Config Value (stored in a persistent table) which is "The most recent Client processed". The job can then
Another job / step can reset the Config Value to BLANK at the start of the next run.
If it is a struggle to encapsulate multiple-steps (Find client, Save config value, Output report for that Client) into one code-block for, for example, SSIS then you might be better off creating a Stored Procedure to contain that code, and executing that instead.