SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by fediori / T-SQL insert using SSIS Data Pump / Latest Posts

RE: T-SQL insert using SSIS Data Pump

Federico Iori — Fri, 08 Feb 2013 02:01:02 GMT

[quote][b]mishaluba (2/7/2013)[/b][hr]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][/quote]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 ...

RE: T-SQL insert using SSIS Data Pump

mishaluba — Thu, 07 Feb 2013 14:54:12 GMT

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]

RE: T-SQL insert using SSIS Data Pump

philcart — Thu, 07 Feb 2013 05:53:30 GMT

Federico, No point in boring people unnecessarily. I've sent you a PM.

RE: T-SQL insert using SSIS Data Pump

Federico Iori — Thu, 07 Feb 2013 04:59:23 GMT

[quote][b]philcart (2/7/2013)[/b][hr][quote][b]nick.mcdermaid (2/6/2013)[/b][hr]SQL Agent is tricky because:1. If you have only one SQL Agent job being your 'host' you can't run mutliple jobs in parallel2. 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 workHere'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.[/quote]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.[quote][b]Federico Iori (2/6/2013)[/b][hr][quote][b]philcart (2/5/2013)[/b][hr]2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.[/quote]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[/quote]That is precisely the wrong approach to take. Forget the gaping security implications of having to keep xp_cmdshell enabled for the [b]whole server[/b]. 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?[/quote]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 ...

RE: T-SQL insert using SSIS Data Pump

nick.mcdermaid — Thu, 07 Feb 2013 04:58:38 GMT

[quote]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.[/quote]As I said if you only have one SQL Agent job there is a problem, as you have many there is not a problem[quote]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.[/quote]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.

RE: T-SQL insert using SSIS Data Pump

philcart — Thu, 07 Feb 2013 04:24:38 GMT

[quote][b]nick.mcdermaid (2/6/2013)[/b][hr]SQL Agent is tricky because:1. If you have only one SQL Agent job being your 'host' you can't run mutliple jobs in parallel2. 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 workHere'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.[/quote]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.[quote][b]Federico Iori (2/6/2013)[/b][hr][quote][b]philcart (2/5/2013)[/b][hr]2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.[/quote]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[/quote]That is precisely the wrong approach to take. Forget the gaping security implications of having to keep xp_cmdshell enabled for the [b]whole server[/b]. 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?

RE: T-SQL insert using SSIS Data Pump

Federico Iori — Thu, 07 Feb 2013 02:07:45 GMT

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 .

RE: T-SQL insert using SSIS Data Pump

nick.mcdermaid — Wed, 06 Feb 2013 21:26:09 GMT

SQL Agent is tricky because:1. If you have only one SQL Agent job being your 'host' you can't run mutliple jobs in parallel2. 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 workHere'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.

RE: T-SQL insert using SSIS Data Pump

Sean Bowden — Wed, 06 Feb 2013 16:20:52 GMT

[quote][b]Federico Iori (2/5/2013)[/b]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[/quote]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).

RE: T-SQL insert using SSIS Data Pump

hdavidson88 — Tue, 05 Feb 2013 09:14:45 GMT

Nice article and I picked up some information I could use in our business. We have different applications that need client data dispersed throughout various locations, which have table table and column names which are not the same as our source database. Our approach was to store the application name, source table name, destination table name, source connection string, destination connection string, source column name, destination column name into a single SQL table. We then call the dynamic package passing the application that we want to load up and the package loops through this mapping table dynamically creating the connections and table and column mappings. Which works fine for table loads, I was looking for information on how to use stored procedures for the data flow task, to update data on destination sources.

RE: T-SQL insert using SSIS Data Pump

Federico Iori — Tue, 05 Feb 2013 09:08:27 GMT

[quote][b]philcart (2/5/2013)[/b][hr]Sorry you lost me on a few fronts.1) you reference another article stating, "The method SSIS Data Pump represents the most effective way to load large amount of data into a SQL Server database because the SSIS Data Pump makes optimal usage of the transaction logs, parallelism, locking, and pipelining. A more detailed explanation is in the referenced article.". Yet I see no such detailed explanation in the referenced article.[/quote]You are right , there is no such detailed explanation. Purpose of my article was not explaining why SSIS Data Pump is the best method. You can give a look to : http://blog.staticvoid.co.nz/2012/8/17/mssql_and_large_insert_statementsand note that SSIS data pump is nothing else than a pipelined bulk copy.[quote]2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.[/quote]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[quote]3) your xp_cmdshell calls are executing a bunch of commands you can do in SSIS with the FileSystem task, or at worst the Script Task.[/quote]Yes, some of xp_cmdshell calls could be moved into BULK_TRANSFER_DATA.dtsx

RE: T-SQL insert using SSIS Data Pump

Luke C — Tue, 05 Feb 2013 06:13:22 GMT

Nice article!

RE: T-SQL insert using SSIS Data Pump

Peter Pirker — Tue, 05 Feb 2013 05:31:43 GMT

Excellent article. When somebody gets around to writing the VB code in C#, could you pls. post it?

RE: T-SQL insert using SSIS Data Pump

philcart — Tue, 05 Feb 2013 04:46:08 GMT

Sorry you lost me on a few fronts.1) you reference another article stating, "The method SSIS Data Pump represents the most effective way to load large amount of data into a SQL Server database because the SSIS Data Pump makes optimal usage of the transaction logs, parallelism, locking, and pipelining. A more detailed explanation is in the referenced article.". Yet I see no such detailed explanation in the referenced article.2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.3) your xp_cmdshell calls are executing a bunch of commands you can do in SSIS with the FileSystem task, or at worst the Script Task.

RE: T-SQL insert using SSIS Data Pump

Federico Iori — Tue, 05 Feb 2013 02:43:58 GMT

It depends how many rows and how many bytes per row you are going to insert. If you start making DWH / ETL with SqlServer, you will probably face this kind of issues ..

RE: T-SQL insert using SSIS Data Pump

nick.mcdermaid — Tue, 05 Feb 2013 02:40:28 GMT

I probably won't have the opportunity to compare myself - thats why I read sites like this. But if I do have an insert performance issue, I might consider SSIS as a solution.Perhaps you could also consider using the SQL Server destination instead of the OLE DB destination in appropriate cases for even mor performance.

RE: T-SQL insert using SSIS Data Pump

Federico Iori — Tue, 05 Feb 2013 01:15:21 GMT

You can try by yourself to compare .. as I have written, Insert Into is fully logged operations, requires more restrictive lock on target table and makes heavy use of transaction logs ..If you insert into an empty table, you can use the hint with(tablock) and the insert will be, under certain conditions, minimally logged, but it puts an X lock on the table to insert into , while OleDb Insert requires only an IS lock and makes better use of transaction logs because commits inserted rows in batch..

RE: T-SQL insert using SSIS Data Pump

nick.mcdermaid — Tue, 05 Feb 2013 00:32:28 GMT

I'd be interested in a performance comparison with a simple INSERT INTO / SELECT solution which does not use SSIS.

T-SQL insert using SSIS Data Pump

Federico Iori — Mon, 04 Feb 2013 23:34:43 GMT

Comments posted to this topic are about the item [B]T-SQL insert using SSIS Data Pump[/B]