Strange disappearance of data from flat file to oledb table

  • Brad Allison

    Hall of Fame

    Points: 3473

    I have not ever seen this one and wondering if anyone has. I have a scheduled SSIS job that transforms two tsv file data and sends it over to a sql table. It has been working for several months now until a few days ago. When trying to troubleshoot missing data, I opened the SSIS project and can see the data in preview of the flat file connection. When I open the connection for the oledb table, there is NO data in Preview. However when I do the exact same thing for the second tsv file that is being transferred in, it works fine. The outside agency that is providing us the files through SFTP did change the first one the other day, but I thought I made necessary changes I need to and still nothing. I even took it as far and doing a NEW SSIS job just to see and still nothing.

    Has anyone seen anything like this and is there anything I should be looking for in the tsv file itself?

    Thanks

  • Phil Parkin

    SSC Guru

    Points: 243688

    Brad Allison (9/15/2016)


    I have not ever seen this one and wondering if anyone has. I have a scheduled SSIS job that transforms two tsv file data and sends it over to a sql table. It has been working for several months now until a few days ago. When trying to troubleshoot missing data, I opened the SSIS project and can see the data in preview of the flat file connection. When I open the connection for the oledb table, there is NO data in Preview. However when I do the exact same thing for the second tsv file that is being transferred in, it works fine. The outside agency that is providing us the files through SFTP did change the first one the other day, but I thought I made necessary changes I need to and still nothing. I even took it as far and doing a NEW SSIS job just to see and still nothing.

    Has anyone seen anything like this and is there anything I should be looking for in the tsv file itself?

    Thanks

    Sounds like the connection is working fine, so suspicion lands on the flat file source in the DF. Try creating a new one and see whether the data magically appears.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Brad Allison

    Hall of Fame

    Points: 3473

    Thanks Phil, I will try that. I did recreate the destination, but not the source. The only thing I did there was refresh the columns. I will let you know

  • Brad Allison

    Hall of Fame

    Points: 3473

    Nope, I deleted the whole Data flow, recreated it and still the same. And this is with a new refreshed tsv file too. I waited yesterday thinking maybe something was going on with that file. So I waited for this morning's new file, but still doing the same.

  • Phil Parkin

    SSC Guru

    Points: 243688

    How odd. If you open the file in Notepad ++, does it look 'OK'?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Brad Allison

    Hall of Fame

    Points: 3473

    Yep, it looks okay. In doing further investigation and trying to trace the issue through execution results, it looks like the permissions on the destination table changed somehow. So what I ended up doing (because I did find out that a column was inserted into the tsv file) was dropping the original table out of SQL and re-creating it with the newer tsv information. And now it works. Frustrating and six hours of work

  • Phil Parkin

    SSC Guru

    Points: 243688

    Brad Allison (9/15/2016)


    Yep, it looks okay. In doing further investigation and trying to trace the issue through execution results, it looks like the permissions on the destination table changed somehow. So what I ended up doing (because I did find out that a column was inserted into the tsv file) was dropping the original table out of SQL and re-creating it with the newer tsv information. And now it works. Frustrating and six hours of work

    One of those days ... we all have them.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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