What things should you consider when deciding which one to use? Here are some of the major design considerations to think about. Some like this because it allows you to make a change in the stored procedure, avoiding having to make a change in SSIS and re-deploying the package. Hi James, Good article. Ease of dev — Again, a matter of opinion.
Thank you for writing this article down. Thanks a lot. I have a question about ETL. I would like to know if there are more steps that go different in a ETL tool than in a database, in order to have some criteria to make a design decision.
Are you moving data from one server to another, between databases on the same server? The question is rather broad and without further input and clarification we really can't give you a really clear answer. Need more information as to what you are trying to achieve. Define a long time too please Koen Verbeeck. Updates are never faster in SSIS. So you end up writing the data to a staging table and do a set based update with TSQL. Which is just the same is doing the update in the database anyway.
Need an answer? This is one of the sample script that i use I don't know how to make more efficient script instead of this PnxCodeValue, j. Without DDL, including all indexes and some actual execution plans we are all flying blind, it is almopst impossible o make any suggestions. Can you try running the select separately from the insert, to at least pin down which is costing you the most.
I'd focus on this within your select list. A correlated sub-query in the select can be horrible for performance and scale very poorly with an increased number of records. One thing I noticed in the code, the haphazard use of table aliases on the columns in the select statement.
Makes it hard to know which table a column is from and could cause a problem if a column with the same name is added to another table used in the query. Based on the first observation, I changed my package. It is exclusively composed of Execute SQL Tasks either with a direct query or with a stored procedure. I have seen as it's reliable, easy to maintain and comparatively fast. There are many things that could cause the performance of a "straight" data flow task and the equivalent Execute SQL Task.
If the problem is easily solved by running queries, then I'd forgo SSIS entirely and write the appropriate stored procedure s and schedule it with SQL Agent and be done. What I still like about using SSIS even for "simple" cases like this is it can ensure a consistent deliverable.
That may not sound like much, but from a maintenance perspective, it can be nice to know that everything that is mucking with the data is contained in these source controlled SSIS packages. I don't have to remember or train the new person that tasks A-C are "simple" so they are stored procs called from a SQL Agent job. Tasks D-J, or was it K, are even simpler than that so it's just "in line" queries in the Agent jobs to load data and then we have packages for the rest of stuff.
Except for the Service Broker thing and some web services, those too update the database. The older I get and the more places I get exposed to, the more I can find value in a consistent, even if overkill, approach to solution delivery. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow.
Learn more. Asked 8 years, 6 months ago. Active 1 year, 8 months ago. Viewed 27k times. I am new to SSIS and have a pair of questions I want to transfer 1,25, rows from one table to another in the same database. Hadi 32k 9 9 gold badges 54 54 silver badges bronze badges. Have you tried breaking the transfer into blocks, e. Logging can be traumatic for large single operations.
0コメント