The new SSIS in SQL2005/SQL2008 are oversized

Question

I studied the new MERGE Statement and there is a nice example for importing a flatfile.

INSERT <Table>
SELECT * FROM OPENROWSET BULK <Import-Flat-File>, <Format-File>...

seems to be a good replacment for such a simple job and avoids to build a SSIS-Package.

EXEC XP_CMDSHELL bcp <Table or View> out <Flat-File> ...

is almost simpler than building an SSIS, isn't it? The old style DTS fitted better for this small jobs.

(I know that the MERGE-Statement doesn't run on a SQL2005)

Do you think that scripting is the best approach for such 'easy things'? Or schould i go deeper into SSIS doing simple import/export's?

Answer 1

Having xp_CmdShell active on a SQL machine is considered a security risk, so I'd avoid that if possible.

I'd do this in SSIS if it were me - It will be a few minutes more work, but you get the ability to version control via source control so the logic of the task is backed up, you'll also get error-handling so you can decide how to handle the error when it inevitably goes wrong.

Also you'll get better with SSIS, no harm practising with minor tasks :)