Post reply

SQL Server - Dynamic SQL Stored Procedure to convert/cast columns

  • July 25, 2018 at 4:16 am

    #414436

    Good morning,

    I have been tasked with creating a stored procedure that will do the following:

    1. Select Source table name, Target table column names, Target table Column data types and DataQualitycheckFlag from a mapping table that we have in place that specifies this information.
    2. Use this information to then check each source table column that is appropriately flagged to be checked one by one to see if the data can be converted/cast to the target column data types successfully, depending on the data type .
    3. If data within the source column cannot be converted, then record this problematic value within another data quality table within another database. This is so we can report back data quality issues.
    4. We will then have a separate process to import the rows that could be converted to the correct data type into the target table.

    I am still relatively new to SQL, however my initial thoughts were to somehow use dynamic SQL within a stored procedure to accomplish this.

    Any push in the right direction would be much appreciated, preferably with an example.

    Thanks.

  • July 25, 2018 at 8:07 am

    #1998944

    Take a look at the article below, seems like it might be a lot easier to just create the process to translate the data and route errors to your data log table. But the article gives you a good way to use your mapping data:

    Dynamic File Column Mapping In SSIS

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

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

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