Databases
StackOverflow2013
Postsdbo
POMethod in SSIS to transform flat file data (based upon scalar sql functions)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMethod in SSIS to transform flat file data (based upon scalar sql functions)
    text
    copied!<p>Initially, I put together a TSQL script to bulk import data from a set of flat files. (see below). Using functions in SQLSERVER it was easy to convert the data to the datatypes I want and to handle transforming the data based upon whatever unique conditions I might encounter with the data set. For example, all columns could have spaces at the beginning or end, the date columns that represent null values are of a specific value, the data that is money can have a dash at the end of the number representing a negative number, etc. So I use the scalar-valued functions in sqlserver to handle the various conditions and case statements to allow a consistent data set.</p> <p>My goal now is to migrate this to SSIS, but it seems extremely cumbersome to replicate what I have already done within SSIS. While SSIS does have tools to do the transforms (like the character map, data conversion, derived column tools, and script component). -- it seems a massive amount of work to get what I already had in my script. It doesn't appear that I can re-use anything. While that might be OK for a small table, keying in the same complex tranformations for 50+ columns per table is really going to be a chore -- and then to maintain that if I need to modify something? uggh. I must be missing something. Thanks for your help.</p> <pre><code> INSERT INTO [TEST1].[dbo].[Table1] SELECT dbo.TRIM([ID]) ,dbo.FixDate([CompleteDate]) ,dbo.TRIM([IsDeleted]) ,dbo.Text2Numeric([QtyTotal]) ,dbo.Text2Numeric([OrderQty]) ,dbo.Text2Money([Price]) FROM OPENROWSET(BULK '\\server1test\temp\import1.txt', FIRSTROW=2, FORMATFILE='\\server1test\temp\import1.Xml' ) as t1 ; GO </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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