September 15, 2016 at 9:15 am
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
September 15, 2016 at 9:23 am
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 15, 2016 at 9:24 am
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
September 15, 2016 at 10:15 am
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.
September 15, 2016 at 10:22 am
How odd. If you open the file in Notepad ++, does it look 'OK'?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 15, 2016 at 11:35 am
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
September 15, 2016 at 11:51 am
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply