Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Gitmo, I may misunderstand your question but will give it a shot. You need to move data from a SQL Server instance to multiple files, correct? You want to leverage the parallelised data movement functionality provided by SSIS. That means multiple simultaneously running Data Flow Tasks (DFTs). For each target file you can have only one DFT because of problems with concurrent writes. </p> <p>To get multiple simultaneously running Data Flow Tasks where your source is a SQL Server database and your target is a set of files, you can possibly try the following ways (please note there are upper limits on the parallelization you can get out of SSIS based upon many factors including your CPU Core count, whether you are running in BIDS/Visual Studio or not, and various settings in your packages, your server(s), your SQL Server instance, and many other considerations):</p> <ol> <li>The Multiple Simultaneous DFT Solution: A single SSIS Package with one Connection Manager pointed to the source SQL Server database and many Connection Managers each pointed to a separate target file, plus one DFT for each target file. The DFTs are all disconnected from one another (no precedence constraints or green/red/blue lines/arrows). If there are pre or post ETL steps that need to run a great way to parallelize these DFTs is to drop them all in a Sequence Container that is connected to the earlier and later tasks through precedence constraints/arrows. These disconnected DFTs in their own Sequence Container will try to all run simultaneously.</li> <li>The Multiple Simultaneous DTEXEC Solution: Multiple SSIS packages each with their own target file-specific DFT. You manually run separate DTEXEC processes either through separate CMD windows or through the GUI. #3 below is a variation on this solution and possibly a better one.</li> <li>The Parent Master Package Running Multiple Children Packages Solution: Wrap the per target file packages developed in #2 above in a single Parent Master package. In the Parent package have multiple simultaneously running Execute Package Tasks. Again these Execute Package Tasks would be disconnected from other tasks. A good way to do this is to drop the multiple Execute Package Tasks in their own Sequence Container. As before if the Execute Package Tasks are disconnected (no precedence constraints/arrows) they will all try to run simultaneously.</li> </ol> <p>Take a look at this excellent article from the Microsoft SQLCAT Team for some more ideas/insight: <a href="http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx" rel="nofollow" title="Top 10 SQL Server Integration Services Best Practices">Top 10 SQL Server Integration Services Best Practices</a> </p> <p>There are likely variations on these same ideas and possibly other solutions available both inside and outside of SSIS. Good luck!</p>
    singulars
    1. This table or related slice is empty.
    1. POSSIS Parallelism - Microsoft HPC Cluster?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USSQLStephenHorne
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi Stephen, Thanks for your answer. This parallelism would always be restricted by the hardware of the SSIS Server, right? I have an Microsoft HPC Cluster at my disposal. If I can write some kind of a distributed application which will get the compute nodes in the cluster to create independent SQL Connection and import a partition of the data, wouldn't it be faster? Any ideas? Can I somehow use Custom Tasks to get the compute nodes in the cluster to do this? These are some ideas floating in my mind, I wanted somebody to validate. Thanks for your answer!
      singulars
      1. PO
      1. USGitmo
    2. COokay, let me add to this. Can I somehow do a remote SSIS package invocation? If somehow we can create ssis package programmatically, i can spawn them on multilple nodes and import data parallely. Any ideas?
      singulars
      1. PO
      1. USGitmo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload