Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
Script Component as a Source?

Answered Script Component as a Source?

  • Tuesday, April 10, 2012 3:22 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I was thinking of using the Script Component as a source to check on the text file format and rearrange the format.

    Source File is a text file in a fixed length delimited.

    For Example: In this case, Row 2, Column 3 supposed to be a date data type, but on the source file, sometimes the Column 2 and 3 are NULL. So in our ETL it will result as a failed because the Column4 is transfers to either Column 2 and 3.

    Input

    Column 1        Column 2        Column 3       Column 4

    ABCDEFG        12/31/2011     01/05/2012    123456

    HIJKLMNO       12/31/2011     123456

    PQRSTUV        123456

    Output

    Column 1        Column 2        Column 3       Column 4

    ABCDEFG        12/31/2011     01/05/2012    123456

    HIJKLMNO       12/31/2011    12/31/9999     123456

    PQRSTUV        12/31/2999    12/31/9999     123456

    Is it possible to do this in Script Component? or maybe there is another way?

    Thanks,

     

All Replies

  • Tuesday, April 10, 2012 3:57 AM
     
     
    Sign In to Vote
    0

    How is NULL reprsented in you fixed length text file?  Text files have no concept of NULL so your script will need rules like tranforming spaces to NULL and other values to date/integer.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

     
  • Tuesday, April 10, 2012 4:12 AM
     
     
    Sign In to Vote
    0

    Hi Dan,

    Yes your correct, the text file doesn't have the NULL representation. 

    The problem is when I use flat file source, Column 2 and/or 3 has the "1233456" value assigned on it already. Can I use script component to at least move the "123456" value to Column 4?

    Thanks,

     
  • Tuesday, April 10, 2012 4:29 AM
     
     
    Sign In to Vote
    0

    I have expalind this very scenario here.

    My Blog    |      Ask Me     |      SSIS Basics     

     
  • Tuesday, April 10, 2012 5:44 AM
     
     
    Sign In to Vote
    0

    Hi Sudeep,

    I check on your post, but we have different scenario.

    Your scenario is basically removing the extra columns and then rearrange the output. But my scenario would be moving the data "123456" in Column 2 or 3 to its proper column which is Column 4.

    Thanks,

     
  • Tuesday, April 10, 2012 6:46 AM
     
     Answered
    Sign In to Vote
    0

    Hi Wookies,

    The scenario is not very different, you need to modify the script I have used.

    Column 1        Column 2        Column 3       Column 4

    ABCDEFG        12/31/2011     01/05/2012    123456

    HIJKLMNO       12/31/2011     123456

    PQRSTUV        123456

    Instead of adding Nulls towards the end of the row,

    you need to Check Column 2, if not date, set it to default date and save the actual value to a variable.

    if column 3 is non date value and column 2 has some value use it else put default date and save the numeric value to a variable

    if column 3 has some date value use it directly,

    If column column 4 has numeric data use it else set the value from the variable you used earlier.

    My Blog    |      Ask Me     |      SSIS Basics