SSIS Package

I got this straightforward SSIS package, that has it  source and its destination as SQL Server Database, the extraction part is via SQL script / query, and hardly any conversion in the middle, and the destination is well defined and the data loads effortlessly, however, the destination table is changing the various "Dates" in the data stream to current system date before it inserts the rest of the records in the target table, very weird behavior.
Example:
From the the source (OLE DB);
OrderDate, CustNumber, TransactionDate, Enrolled
06/01/2015 131333            01/01/2008            Y
06/01/2015 191999            07/21/2011            Y

once they're in the Target table, they'd look like this;
OrderDate, CustNumber, TransactionDate, Enrolled
06/04/2015 131333            06/04/2015            Y
06/04/2015 191999            06/04/2015            Y


Your thoughts.
Thx
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thoughts

1.

Check the destination table to see if there is an insert trigger that sets the value of columns OrderDate and TransactionDate to GETDATE().  I could see this happening for a back-end where the assumption that the only data entry that would occur would be from the front-end, and not an ETL package.

2.

Just for kicks and giggles check the SSIS destination task again to make sure the mapping is done correctly, and that the columns are not being fed from a variable.  Never hurts to turn the data viewer on between source and destination, run, and look for yourself.

3.

This swinging chair is really comfortable, and the beer is starting to go down, and it's probably #1 so I'll stop here.

4.

If #1 is correct then you may want to re-evaluate the wisdom of bulk inserting rows into this table, just in case it causes problems as your app does other things to other tables that the SSIS package wouldn't know about and is not doing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roberto Madro R.Programmer AnalystAuthor Commented:
Jim;

I checked out the destination table and found no Trigger, and as for the column mapping that seems fine because the data loads fine, but when the data is inserted into the destination table, the dates get changed into system date / current date.

We crack this one, and I'll send you a selection of the best brew in town, we've got 4 renowned micro-breweries here :-)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check is those columns doesn't have a default constraint. But for the default constraint put the system date means that no values are being imported for those columns.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
Had to resort to scripting the whole thing as the project requirements have changed, however, EE and all of its experts have always been a valuable source of info and for that I thank you both.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.