SSIS Pull strange, Corrupted source file???

Posted on 2010-01-12
Medium Priority
Last Modified: 2013-11-10
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)
Question by:moosetracker
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Assisted Solution

rajvja earned 800 total points
ID: 26292019
Check the datatype and size of the problem field. Try changing to sufficient type and size.
I got the same problem and resolved using this.
LVL 30

Expert Comment

by:Reza Rad
ID: 26292058
could you upload your package and the corrupted file here?
LVL 22

Assisted Solution

PedroCGD earned 200 total points
ID: 26292196
What is the error message?
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Author Comment

ID: 26293752
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

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?


Author Comment

ID: 26293995
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.  

Accepted Solution

moosetracker earned 0 total points
ID: 26295283
I've narrowed the pull down to
Select * from artransaction where Profit_no = 1 and log_date >= 3/1/2006  and log_date <= 3/31/2006

as having something in it that makes all the records pulled Null out the Ad_no field..

This was the first month we went live with this system...  Hmm..
Thanks guys.. I am really leaning toward corrupted data at this point..
LVL 22

Expert Comment

ID: 26295339
always welcome!

Assisted Solution

grzegorzs earned 1000 total points
ID: 26309145
Place a grid view data viewer after source and see what leaves the source. It maybe a problem with datatypes or conversion. Maybe there is value in source file which is misinterpreted because it contains a special character (I had similar problem: I had comma decimal seperator in numbers and the comma was also a column seperator...bad luck...)

best regards

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question