SSIS package creation with txt file having multiple select statements

Question

SSIS package creation with txt file having multiple select statements

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

below86 SSChampion Points: 11532 More actions · Answer 1

Brandie Tarvin (3/21/2016)
below86 (3/16/2016)
Brandie Tarvin (3/15/2016)
aavi1011 (3/14/2016)
App team provided select statements and want output of select statement as report in xls or csv format to thier email ID
Is this a one off run or something that will happen regularly?
If it's a one off, just use the Export Wizard. You'll have to do it once for each query.
If it's a regular thing, then do as I suggested earlier. Create Stored Procedures as your Source, then use the flat files as your Destination.
Why not just put the Select statements in the source instead of calling a SP? If you don't have permission to set up a SP on the production server this may be the way to go.
Overhead. Security issues. Ease of maintenance.
below86 (3/16/2016)
You would then need to set up a dataflow for each select you need to do.
Nope. One Dataflow Task can be used for all (if you're into self-torture). It's just a matter of cramming in all the sources and destinations into the DF. I've done it before. The agonizing thing about this is you can't disable any of the transformations within the Dataflow task for troubleshooting purposes (hence the self-torture comment).

I'm not into self-torture, so I would create separate one for each, put them in a container if I wanted them to execute at the same time.

And if you don't have the rights to create the SP on the server? I agree with you about the maintenance, it's a pain when this stuff is inside the SSIS package, I'll set up a SP or have it execute the .SQL file from a folder if I have that option.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Brandie Tarvin SSC Guru Points: 173056 More actions · Answer 2

below86 (3/21/2016)
And if you don't have the rights to create the SP on the server?

I presume the company in question would have an SDLC process to move something from Dev to QA/UAT to Production. So the OP doesn't need permissions in production. As long as the procs can be developed in Dev and then pushed via the regular process to production.

On the other hand, if the OP is a DBA without even Dev permissions, then I don't know how to address that issue without involving another DBA who does have those perms.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.