Search code examples
sql-serverssisstaging-table

Designing a staging table to import data

I'm using SQL Server 2012 Standard Edition After setting an SSIS package that imports data from a flat file, I wanted to know how would I proceed to insert data into staging tables and what the structure of staging tables is?

Solution

  • It's quite hard to answer your question without any sample input/output but in general, I would create the temp stage table with the same data types to match the source files (as destination-data mentioned above). Then, you can use Derived Column Transformation to transform the data to match your business logic before using a Data Flow Task to insert the transformed data into your destination table(s).

    You can add error handling to the Derived Column step by selecting 'Configure Error Output', there is a good example of how to do this here:

    Hope this helps.