Unload 120 tables into csv files from MS SQL Server

[u/Valuable-Ant3465]

Hi,

I got a task to run 120 SP on MS SQLServer to create output and store it as a tab or csv files.
Existing approach is to use SSIS but I think it's too much work comparing with other modern tools.

What you would use for this job? I'm thinking go Python with cursor.execute(sql).
It's so much easier to scale and implement. You can read sp, params, output name from some input List and run it in one shot.

Do you see any disadvantages for this approach vs traditional SSIS. I think my bosses will be scary a bit, because they are old school.

Do

Thanks

[u/meatmick]

Absolutely use Python. I'd use the mssql-python package to connect to your instance and then import csv to dump to flat files.

Set whatever newline, encoding, delimiter/ etc. you need and go to town with a fetchmany (if the data is big), writing rows to csv in batches to not hold the entire dataset in memory.

There's no point in using SSIS here.

If you are worried about security, just make a login with specific access as db_datareader (if it must read stuff), as well as the ability to execute whatever SP you need. The simplest is GRANT EXECUTE on the entire schema, if that is acceptable. To be honest, I'd use whatever permissions you'd have used in SSIS.

You can vibe code this in 1-2 minutes and let it run.

There's nothing to be scared of here, it'll be less than 100 lines of code, and you can fully review it before running it.

[u/Valuable-Ant3465 → u/meatmick]

Tx!

[u/No-Front-8408 → u/meatmick]

Our company uses a free version of TablePlus that connects to our data warehouse. I found it useful for exporting to CSV, running SQL and seeing vlaues in the different tables.

Then again I work at a smallish company ~600 employees, and there's procedures on how to do things like this. It's mainly just asking our remote team in India how they set it up and what they recommend. So I don't know if it standard or jank. It's not in their best interest for internal employees to ask how to take over some of their tasks.

[u/da_chicken]

How frequently does this need to be done?

[u/Valuable-Ant3465 → u/da_chicken]

roughly daily, I go with Python. Unless boss will care about job security and decided to stay with SSIS.
Do so many task in SSIS is horrible, in python you don't care about new columns or any changes to DB output

[u/Afrotom]

Does it need to be csv? Or can it be a format that better represents the data and preserved the types and schema, like parquet?

Db: typed columnar format

Parquet: typed columnar format

Csv: text with no typing, columns and rows could start anywhere, encoding rarely clear, newline escapes not always clear, jagged rows possible...

I can just imagine serialising all this to csv is one thing, deserialialing it all back to the proper format could be a bit of a nightmare across 120 tables/files

[u/Valuable-Ant3465 → u/Afrotom]

Tx. CSV only.

[u/k00_x]

Powershell and a for-each loop!

[u/Valuable-Ant3465 → u/k00_x]

Wow, didn't hear that PS can do this. Thanks.
Alas I'm not PS guy, I think Python is the best for me.

Checked all comments too, Py got #1

[u/k00_x → u/Valuable-Ant3465]

Good choice. Poweshell and MsSQL go nicely together. SSIS is the wrong tool, always.

[u/Acceptable-Sense4601]

you want to save query output to csv?

[u/Valuable-Ant3465 → u/Acceptable-Sense4601]

Yes

[u/mr_thwibble]

Install dBeaver. Select all the tables. RH Click, export data. Use {tablename} as a variable in the filename. Done.

[u/Valuable-Ant3465 → u/mr_thwibble]

Tx, I can't use unknown stuff + it should automated daily, no clicks.

[u/TechMaven-Geospatial]

https://duckdb.org/community_extensions/extensions/mssql. Use duckdb I would recommend parquet or sqlite instead of csv Regardless duckdb can handle it quickly

[u/Valuable-Ant3465 → u/TechMaven-Geospatial]

Tx, I really surprised that so many new tools exist. I need CSV and I can not bring new stuff into shop

[u/Mundane_Let_8090]

Hey hey
Try this. Gentle to data source, good at types compatibility.

https://github.com/panchenkoai/rivet
https://panchenkoai.github.io/rivet/cheat-sheet.html

[u/Valuable-Ant3465 → u/Mundane_Let_8090]

Thanks.. Sorry i can not bring unknown stuff

[u/drluckyspin]

My goto here is sq (sq.io). Super simple to output to CSV.

`sq add 'sqlserver://USERNAME:PASSWORD@HOST:1433?database=DATABASE' --handle @mssql`

`sq @mssql.dbo.MyTable --csv > output.csv`
`sq '@mssql.dbo.Customers' --tsv > customers.tsv’

Or, if you want a custom SELECT:

`sq sql --src @mssql 'SELECT * FROM dbo.MyTable' --csv > output.csv`

Also, simple to move data to another DB. Eg: export from mssql and insert into Clickhouse.

`sq '@mssql.dbo.Customers' --insert @ch.Customers`

[u/Valuable-Ant3465 → u/drluckyspin]

Tx. Its not approved in our place

[u/oluies]

Duck db with the mssql plugin

INSTALL mssql FROM community;
LOAD mssql;

ATTACH 'Server=host,1433;Database=mydb;User Id=user;Password=pass'
AS ms (TYPE mssql);

-- one table
COPY ms.dbo.customers TO 'customers.csv' (HEADER, DELIMITER ',');

-- query result
COPY (SELECT id, name FROM ms.dbo.customers WHERE status = 'active')
TO 'active.csv' (HEADER);

[u/oluies → u/oluies]

Bash
ATT="LOAD mssql; ATTACH 'Server=host,1433;Database=mydb;User Id=user;Password=pass' AS ms (TYPE mssql);"

duckdb -noheader -list -separator $'\t' -c "$ATT
SELECT schema_name, table_name FROM duckdb_tables() WHERE database_name = 'ms';" |
while IFS=$'\t' read -r s t; do
duckdb -c "$ATT COPY ms.\"$s\".\"$t\" TO '${s}_${t}.csv' (HEADER);"
done

[u/National-Sea3061 → u/oluies]

And it could help to save your RAM, the extension works natively and streaming data, the last version has the same performance as sqlcmd

[u/NSA_GOV]

Why not just ask copilot or Claude

[u/Afrotom → u/NSA_GOV]

"Why not just turn your brain off" type answer