none
SSIS package runs successfully but no data in table RRS feed

  • Question

  • Forum,

    This is a very peculiar problem, my source is TXT file and loading data to SQL server table. The previewer shows data and preview shows data. It shows that data is passed to destination but not written in the tables . Has any one gone thru this situation what was the fix ?

    I have tried to convert the data type too but no luck.

    i have worked with SSIS for a few years but this is a shocker . thanks in advance 

    

    Thursday, September 5, 2019 7:32 PM

All replies

  • Hi MSSQLBIDWH,

    I had the same experience in the scenario where "dirty" data from the *.csv file contained invisible null terminators '\0' in the middle of actual data.

    The solution was very obvious.

    We switched to data feeds in XML format, and had their data quality enforced by XSD contracts.


    SSIS has XML Source Adapter and XML Task.

    Validate XML with the XML Task
    https://docs.microsoft.com/en-us/sql/integration-services/control-flow/validate-xml-with-the-xml-task?view=sql-server-2017

    This way, invalid data feeds stay, until they fixed, at the system of origin, i.e. sender, and don't come to you, system of destination, i.e. receiver.



    Thursday, September 5, 2019 8:22 PM
  • Hi MSSQLBIDWH,

    Could you please share the warning message in OLEDB Destination?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 6, 2019 1:48 AM
  • truncation may occur due to inserting data from data flow column with a length of 31,

    I can use advanced options and ignore the failure

    Friday, September 6, 2019 12:17 PM
  • truncation may occur due to inserting data from data flow column with a length of 31,

    I can use advanced options and ignore the failure

    Put it there a flat file destination instwad or an extra split to a flat file and visually check whether the data look fit for the table.

    Then if yes, take a row and try to insert manually into the table - see what happens (I hope there are no triggers on the table).


    Arthur

    MyBlog


    Twitter

    Friday, September 6, 2019 1:17 PM
    Moderator
  • no triggers on the table ..as its a flatfile i imported all the results into a staging table most fileds are varcha and then imported to target table with the correct data types .

    thats how i got it working 

    sorry for the late response

    Tuesday, September 17, 2019 8:22 PM
  • Hi MSSQLBIDWH,

    Thanks for the update.

    Though it is still not clear what was causing the problem in your environment.

    If it is a data type discrepancy, SSIS would complain, but it is not the case.

    "...I had the same experience in the scenario where "dirty" data from the *.csv file contained invisible null terminators '\0' in the middle of actual data..."

    Here is a useful data cleansing user defined function for you:

    /*
    1. All invisible TAB, Carriage Return, and Line Feed characters will be replaced with spaces.
    2. Then leading and trailing spaces are removed from the value. 
    3. Further, contiguous occurrences of more than one space will be replaced with a single space.
    */
    CREATE FUNCTION dbo.udf_tokenize(@input VARCHAR(MAX))
           RETURNS VARCHAR(MAX)
    AS
    BEGIN 
           RETURN (SELECT CAST('<r><![CDATA[' + @input + ']]></r>' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)'));
    END
    
    -- test
    DECLARE @input VARCHAR(MAX) = '   dog    is             barking  ;  ]'
    SELECT dbo.udf_tokenize(@input);




    Tuesday, September 17, 2019 9:14 PM