Updating data using ssis
I'm new to SSIS and need a suggestion on which way to proceed to resolve the following problem.
I can lookup the documentation and implement it, just need to be pointed in the right direction within the SSIS framework.
When you bring in the Ole Db command into the picture in a data flow, you’ll be firing the statement in this control once for every row of data in your pipeline.
So let’s say our source retrieves a million rows into the data flow: the UPDATE command inside the OLEDB Command component will be executed one million times!
Similarly, when you code transformations—the T in ETL (extraction, transformation, and loading)—you are limited only by your imagination and the data available.
SQL Server Integration Services (SSIS) provides powerful tools for transforming the raw Play-Doh of data into useful, meaningful tools to help a business thrive.
The above update has us retrieving data from some source, and then running an update on our target database based on one or more values in the source.
There are several ways to go about updating data with SSIS, one of which (the staging table method) requires a bit more setup but can also perform significantly better.This has been beneficial for us because in our environment because our business users provide data to us in the form of Excel spreadsheets.Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases.It's really the bridge with using flat files and SSIS (automating the import) that I'm looking for here.Just to give a quick background of what I ultimately am trying to accomplish: We do exports of our data into flat files and those will go to a ftp server.