hi, i'm archiving some dw stuff to tsv's. i got the manual sequence working where you pick tasks->export->...
i have to run it that way for multiple erps to separate files (datestamp on file name) one for each erp, probably with a schema recorded in a higher level folder. I would have liked to see a "script as button" in addition to the ssis choices (catalog vs file system).
is there a way to script the sequence im following ? whats underneath the covers bcp, ssis something else when you run immediately?
As far as I know SSIS cannot be scripted. SSIS is it's own thing. The export method you are using is designed to be an infrequent export option. If you need to frequently export data, I recommend building an SSIS package to do the export and then you can schedule it. PLUS if you build an SSIS package for it, you can have it export everything all at once; no need to do it for each ERP.
Once you have the SSIS package(s) created, you can use a SQL Agent job to schedule them and if you need to script it, you can use a script to call the job.
When I say SSIS cannot be scripted, I mean that the export method you are using cannot be scripted. SSIS objects cannot be created via script; they need visual studio. BUT once they are created, you can use a script to execute the SSIS package no problem. I just prefer it through a job as then I can schedule it and don't need to think twice about it.
At least that's my recommendation.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 26, 2025 at 5:32 pm
Forgot one thing that I don't recommend, but would kind of be an option - xp_cmdshell. While I strongly advise against it, you COULD use xp_cmdshell to export data to disk but as a general rule, I recommend having xp_cmdshell off unless there are no other options to do what you want to do. In your case, SSIS is the tool I'd recommend.
You COULD also use SSRS and set up a scheduled report to export data to disk, but that feels like a lot of overhead when SSIS can do it all for you without the overhead of trying to maintain an SSRS report. Not scripting (no TSQL for example), but it is also an option.
SSIS is the right tool for this job.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy