Strange disappearance of data from flat file to oledb table

Question

Strange disappearance of data from flat file to oledb table

Brad Allison

Hall of Fame

Points: 3550
More actions
September 15, 2016 at 9:15 am

#321477

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Brad Allison Hall of Fame Points: 3550 More actions · Answer 2

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: 3550 More actions · Answer 3

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: 246960 More actions · Answer 4

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Brad Allison Hall of Fame Points: 3550 More actions · Answer 5

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: 246960 More actions · Answer 6

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin