Post reply

what is the best practice for changing the variable length of a column used in various SSIS and SSAS tasks?

  • January 2, 2019 at 4:46 am

    #416594

    Ahoi,

    Request:
    I have a column that needs a change of length for future changes, because of changes of that column in the source.
    So basically  there will be 4 char  values in the future compared to current 3 char ones.

    What ive done so far (developmentserver):
    Checking all tables that need the change, by looking for all tables having the column (different name in source and bi, so both need a column length change)
    select distinct TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME in ('COLUMNNAMEINSOURCE','COLUMNNAMEINBI')

    Altering the tables:

    ALTER TABLE dwh.FACT_AE ALTER COLUMN Verkaeufergruppe_id nvarchar(4);

    Issues i have encountered (so far):

    • To my simple mind i had to realize, the views have an actual static char size and are not dynamic, which means i have to alter those aswell
    • If the table/view is used in SSIS, i have to manually open the Source/Target and confirm to get rid of the cut off warning
    • If there is a derived column based on this column i also have to manually recreate that column to change the column size from 3 to 4
    • Trying to get rid of the cut off warning does not work, when the source is a view and not a table

    My questions:

    1. Is there some best practice for this task? Some way to reduce the effort and potential risks with forgetting something.
    2. If i add columns in the future and the source is f.e. 3 char size, should i make it default 4 or 5 to prevent issues like this or is there something speaking against this practice?
    3. Where does SSIS save the columns, when the source element is a view? --> "SOLVED" see at very bottom

    • in the source i have to define source and destination column, i guess the original purpose was renaming i guess

    • here i still get the following error, realtering the view, after altering the table already, which changed the column length from 3 to 4 as intended

    Schweregrad    Code    Beschreibung    Projekt    Datei    Zeile
    Warnung        Überprüfungswarnung Kunde_VV_QS: {5C13983E-58DD-4E71-AC77-126EE1D86FEE}: Daten werden möglicherweise abgeschnitten, weil Daten aus der Verkaeufergruppe_ID-Datenbankspalte mit der Länge 4 in die Verkaeufergruppe_ID-Datenflussspalte mit der Länge 3 abgerufen werden.        DWH_MD_Kunde 1.dtsx    0

    English TLDR:  Data cut off because length 4  is trying to be written into a target with data length 3

    • The destination element which is obviously is a table had the warning aswell, but this was removed after opening it in SSIS and confirming --> SSIS realized the column length was changed from 3 to 4

    What i did:
    - delete Source element and recreate it, it caused like followup errors --> if there was a union i had to delete and recreate that union column, even worse, if there was another union i had to to that there aswell

    I want to be the very best
    Like no one ever was

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • January 8, 2019 at 3:49 am

    #2018263

    I don't think there is any best practice for this except better design. Why are you now changing a char(3) to a char(4)? Could you have anticipated this from the beginning?

    As for the SSIS, you can open the XML and search through that for your field, if you change it there, there is no need to open the packages themselves as when it updates meta-data, it is simply updating the xml in the background, including derived fields etc.

    Hope this helps.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply