SQL tables not showing data but data shows in ADF

Anonymous
2022-04-22T19:41:03.29+00:00

Hello,

I am relatively new to using Azure, currently working on a data migration project. I have managed to create a pipeline that ingests data and dumps it into a SQL database. However, I am currently having issues with some data from some columns not showing up in their respective SQL tables or the columns disappearing from the sink.

The strange thing is that 1) the source data does have full data in these particular columns 2) in the sink, the column completely disappears even though it is in the mapping OR 3) if the column doesn't go missing in the sink, the data shows up in the sink (data preview) but not in the SQL table.

Below are some images to illustrate:

Source file with data in the data preview (using col_idnum)
195664-adfsourcedata.png

Column disappears in sink, data preview (missing col_idnum)
195619-adfmissingcolumn.png

Column reappears in SQL table but without data (col_idnum)
195694-sqlmissingdata.png

Data type matches in the data flow projection and in the SQL schema
195712-adfdatatype.png
195702-sqldatatype.png

Colum shows up in the sink mapping
195665-adfmapping.png

OTHER EXAMPLE
The column doesn't disappear and shows data in the sink data preview (col_indsutry_pirl)
195620-adfsinkdatashows.png

Data disappears in the SQL table (col_indsutry_pirl)
195666-sqlmissingdata-indsutry.png

The data flow being used (very simple transformation)
195703-dataflow.png

Hoping this is enough detail, but please if more detail is needed feel free to ask. Currently, I have around 15 SQL tables and not all have this issue, but we are getting quite a few with these issues so any help would be appreciated. And a thank you to anyone who can help me solve this!

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,618 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2022-04-24T12:15:30.19+00:00

    (1) Do you use Azure SQL Database or SQL Server on premises?
    Please use the correct tags and don't add extra irrelevant tags
    At this point you select both tags
    The answers which you get are related to the products and services which you inform us that you use according to the tags

    SQL tables not showing data

    SQL Server never shows data since it has no GUI. SQL Server is a set of services which run in the background. To see the data we are using client tools like SSMS for example. We can send queries to the server to return the data set and the client tool which we use will present it.

    However, I am currently having issues with some data from some columns not showing up in their respective SQL tables or the columns disappearing from the sink.

    How do you watch the data?
    Are you sure that the data was inserted to the SQL Server table well?

    My guess is that there are issue related the data type

    Can be issue with mapping of the columns during the migration of the data, which result in attempting to insert a wrong data type from the source table, or simply the data type in the target table do not match the source table. For example NVARCHAR(100) vs NVARCHAR (1000)

    These issue can lead to cases that the data was not inserted to specific column(s)

    in the sink, the column completely disappears even though it is in the mapping

    The mapping do not shows the entire data but usually only several rows. The issue might be in the values in other rows.

    What next?

    (1) Please explain what tool you use to check the data in the target table and present the query which you use for the task

    (2) Try to import only a few rows (if needed create a copy of the table and insert only a few rows and try to do the migration on that table

    (3) If 2 worked then try to check if some of the rows might include values which do not fit the target table

    (4) Check the exact data types of the source and the target tables

    0 comments