moosetracker
asked on
SSIS Pull strange, Corrupted source file???
Just wondering if anyone else has hit this problem, and can either tell me what is wrong with SSIS, or if this is a sign of a corrupted source file.
We are pulling over 30 files all fine except for one. All the fields in the problem file are mapped correctly in the source, and in the destination... But one field pulls in null if the Selection asks for a pull of all the records (about 850,000).. Yet with just changing the selection from "Select * from table" to Select * from table where log_date >= yesterday I pull about 2,000 records with the problem field displaying the ad number it should be.
Background on this table, we are converting from data pulls through Visual Studion statements that we used until we found the wonders of SSIS.. This is still working since we are working the kinks out of our new pull (kinks like this one).. The same file went from a 1 1/2 hour pull when we did the full pull once a month, to this week takeing 2 days to pull, but the problem field did pull fine..
Now if I do a SQL cmdShell command pull of the file, it seem to pull fine.
But, with 2 strange unexplainable problems on the same file I am leaning toward the source file being corrupted, maybe on this problem field. I was just hoping some of you SSIS, SQL gurus might be able to give me some advice on other ways to test this theroy out..
PS.. the problem table is not in SQL.. I am connecting to it through ODBC.. (ie the cmdShell pull)
We are pulling over 30 files all fine except for one. All the fields in the problem file are mapped correctly in the source, and in the destination... But one field pulls in null if the Selection asks for a pull of all the records (about 850,000).. Yet with just changing the selection from "Select * from table" to Select * from table where log_date >= yesterday I pull about 2,000 records with the problem field displaying the ad number it should be.
Background on this table, we are converting from data pulls through Visual Studion statements that we used until we found the wonders of SSIS.. This is still working since we are working the kinks out of our new pull (kinks like this one).. The same file went from a 1 1/2 hour pull when we did the full pull once a month, to this week takeing 2 days to pull, but the problem field did pull fine..
Now if I do a SQL cmdShell command pull of the file, it seem to pull fine.
But, with 2 strange unexplainable problems on the same file I am leaning toward the source file being corrupted, maybe on this problem field. I was just hoping some of you SSIS, SQL gurus might be able to give me some advice on other ways to test this theroy out..
PS.. the problem table is not in SQL.. I am connecting to it through ODBC.. (ie the cmdShell pull)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Reza Rad
could you upload your package and the corrupted file here?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No Error message. Runs successfully, no matter what. It is just that if I use the following statement in the
Select * from artransaction where Profit_no = 1 and log_date > 1/1/2010 .. All is ok
Select * from artransaction where Profit_no = 1 and log_date > 1/1/2009 and log_date <= 12/31/2009 .. All is ok
Select * from artransaction where Profit_no = 1 and log_date > 1/1/2008 and log_date <= 12/31/2008
But
Select * from artransaction where Profit_no = 1 ... I get all the records, only for one field (Ad_no) the field is only null values.. Yet for the 2 filtered Select statements while some fields are null, about 90% is filled with a number.
I can not upload the table.. It's on a server I have limited access to, plus that would be mega huge.
I would be willing to upload the package, but the Attach file does not see the extension of the package as one it accepts, so how would I do that?
Select * from artransaction where Profit_no = 1 and log_date > 1/1/2010 .. All is ok
Select * from artransaction where Profit_no = 1 and log_date > 1/1/2009 and log_date <= 12/31/2009 .. All is ok
Select * from artransaction where Profit_no = 1 and log_date > 1/1/2008 and log_date <= 12/31/2008
But
Select * from artransaction where Profit_no = 1 ... I get all the records, only for one field (Ad_no) the field is only null values.. Yet for the 2 filtered Select statements while some fields are null, about 90% is filled with a number.
I can not upload the table.. It's on a server I have limited access to, plus that would be mega huge.
I would be willing to upload the package, but the Attach file does not see the extension of the package as one it accepts, so how would I do that?
ASKER
I have narrowed down the problem is in the year 2006
Select * from artransaction where Profit_no = 1 and log_date >= 1/1/2006 and log_date <= 12/31/2006
Has the problem of Nulling out all the data in the Ad_no field.. Now I will try to narrow down to the month the problem is in.
Select * from artransaction where Profit_no = 1 and log_date >= 1/1/2006 and log_date <= 12/31/2006
Has the problem of Nulling out all the data in the Ad_no field.. Now I will try to narrow down to the month the problem is in.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:-)
always welcome!
Pedro
always welcome!
Pedro
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.