SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL insert using SSIS Data Pump


T-SQL insert using SSIS Data Pump

Author
Message
Sean Bowden
Sean Bowden
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 450
Federico Iori (2/5/2013)
How could I launch from T-SQL a SSIS package ? The only way I know is using xp_cmdshell.. purpose of the article was a T-SQL stored procedure able to launch a SSIS package, and this requires xp_cmdshell


We use SQL Agent to launch and manage our SSIS packages, it's pretty trivial and easy to implement (and you could easily make it dynamic and adapt into your system).
nick.mcdermaid
nick.mcdermaid
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 778
SQL Agent is tricky because:

1. If you have only one SQL Agent job being your 'host' you can't run mutliple jobs in parallel
2. If you want to have a non power-user trigger a SQL Agent job from some kind of client you need quite a bit of custom plumbing to get it all to work

Here's another idea: use BULK INSERT instead of SSIS.


But anyway all of these various methods of loading data have already been covered ad nauseum elsewhere.
Federico Iori
Federico Iori
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 300
BULK INSERT on Windows OS is not pipelined : first you need to save all to a file, then Bulk Insert from the file.
SSIS use C# to achieve pipelining .
philcart
philcart
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4390 Visits: 1436
nick.mcdermaid (2/6/2013)
SQL Agent is tricky because:

1. If you have only one SQL Agent job being your 'host' you can't run mutliple jobs in parallel
2. If you want to have a non power-user trigger a SQL Agent job from some kind of client you need quite a bit of custom plumbing to get it all to work

Here's another idea: use BULK INSERT instead of SSIS.


But anyway all of these various methods of loading data have already been covered ad nauseum elsewhere.

We load end-of-day transaction files from multiple banks using the same package. We have multiple SQL Agent jobs and use the /config parameter to provide a different config file for each bank. Package has no problems executing in parallel.

As for non-power users managing the jobs, that's done from a web-page that calls sp_start_job. The app pool identity of the site has the permissions required to manage the jobs.


Federico Iori (2/6/2013)
philcart (2/5/2013)

2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.

How could I launch from T-SQL a SSIS package ? The only way I know is using xp_cmdshell.. purpose of the article was a T-SQL stored procedure able to launch a SSIS package, and this requires xp_cmdshell


That is precisely the wrong approach to take. Forget the gaping security implications of having to keep xp_cmdshell enabled for the whole server. Why on earth would you contemplate launching an executable within the memory space of the Database Engine, consume it's resources and possibly adversely affect load performance?

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
nick.mcdermaid
nick.mcdermaid
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 778
We load end-of-day transaction files from multiple banks using the same package. We have multiple SQL Agent jobs and use the /config parameter to provide a different config file for each bank. Package has no problems executing in parallel.


As I said if you only have one SQL Agent job there is a problem, as you have many there is not a problem

As for non-power users managing the jobs, that's done from a web-page that calls sp_start_job. The app pool identity of the site has the permissions required to manage the jobs.


I would call building a web app 'custom plumbing'

Anyway I don't think we disagree here. When we get down to specifics every solution has it's advantages and disadvantages.
Federico Iori
Federico Iori
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 300
philcart (2/7/2013)
nick.mcdermaid (2/6/2013)
SQL Agent is tricky because:

1. If you have only one SQL Agent job being your 'host' you can't run mutliple jobs in parallel
2. If you want to have a non power-user trigger a SQL Agent job from some kind of client you need quite a bit of custom plumbing to get it all to work

Here's another idea: use BULK INSERT instead of SSIS.


But anyway all of these various methods of loading data have already been covered ad nauseum elsewhere.

We load end-of-day transaction files from multiple banks using the same package. We have multiple SQL Agent jobs and use the /config parameter to provide a different config file for each bank. Package has no problems executing in parallel.

As for non-power users managing the jobs, that's done from a web-page that calls sp_start_job. The app pool identity of the site has the permissions required to manage the jobs.


Federico Iori (2/6/2013)
philcart (2/5/2013)

2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.

How could I launch from T-SQL a SSIS package ? The only way I know is using xp_cmdshell.. purpose of the article was a T-SQL stored procedure able to launch a SSIS package, and this requires xp_cmdshell


That is precisely the wrong approach to take. Forget the gaping security implications of having to keep xp_cmdshell enabled for the whole server. Why on earth would you contemplate launching an executable within the memory space of the Database Engine, consume it's resources and possibly adversely affect load performance?


PhilCart, your idea is :
T-SQL stored procedure should
1) Create with dynamic SQL and launch syncronously a job that launches BULK_TRANSFER_DATA.dtsx
2) Create with dynamic SQL and launch ( possibly syncronously) a second job that launches the SSIS package generated at point 1 and saved into a specific folder
3) Delete the 2 jobs and the created dtsx


It is possible of course, maybe safer than xp_cmdshell, but requires some coding ...
Would you try to provide it ? My article does not indicate the way, but offers a working solution..
Moreover, as long as I know, a SSIS package can create another SSIS package only to the file system ...
philcart
philcart
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4390 Visits: 1436
Federico, No point in boring people unnecessarily. I've sent you a PM.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Misha_SQL
Misha_SQL
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1030 Visits: 1006
Thank you for the article! Note that if you are using SQL 2012 there is now a way to invoke SSIS package from T-SQL without having to resort to xp_cmdshell: [url=http://sqlblog.com/blogs/davide_mauri/archive/2011/11/24/execute-a-ssis-package-in-sync-or-async-mode-from-sql-server-2012.aspx][/url]



Federico Iori
Federico Iori
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 300
mishaluba (2/7/2013)
Thank you for the article! Note that if you are using SQL 2012 there is now a way to invoke SSIS package from T-SQL without having to resort to xp_cmdshell: [url=http://sqlblog.com/blogs/davide_mauri/archive/2011/11/24/execute-a-ssis-package-in-sync-or-async-mode-from-sql-server-2012.aspx][/url]


Nice to know Mishaluba, by the way I know personally Davide Mauri :-)
I am not sure, anyway, if a SSIS package can create dynamically another SSIS package ( my case ) and save it elsewhere than the operating system ...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search