January 27, 2012 at 2:09 pm
Hello Everyone,
Hope all is well.
I created an SSIS package with OLEDB Source and OLEDB destination. All the data from the source will get pushed to the destination table. But the condition I used in my query of the OLEDB source has a couple of conditions:
One that goes by getdate()-1 and other goes by an ID where if the ID exists in the destination table it should not insert. SO whenever I run the package, whatever new(delta) data is present it should get pushed to the destination. But in my case the package executes fine when I run it first time. But errors out on the OLEDB destination when I run it the second time.
I dont understand what am i missing here.
Your valuable inputs are much appreciated.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 28, 2012 at 9:36 am
I was trying to use a conditional split to filter out data based on a timestamp column for Getdate()-3 and I am getting this error.
The data type "DT_DBTIMESTAMP" cannot be used with binary operator "-". The type of one or both of the operands is not supported for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
Can someone help me how to cast it in the conditional split.
Thanks.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 28, 2012 at 10:06 am
So is it the case that you only ever want to insert new data and that you are not worried about updates?
Is the PK ID an identity column? Just wondering whether you could query the target data (select max(ID) etc.) and then just use that to select from source where ID > max(ID)?
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
January 28, 2012 at 10:29 am
Thanks Phil for your response. Let me post what I am doing real quick for you.
I am only trying to insert new records from the source to the destination table. My Scenario is the database server is same but its between two different databases. My Query that I am using in the OLEDB source is pasted below:
SELECT
test1.PhoneLogID,
test2.PhoneNumberID,
test3.PersonID,
test1.JobID,
test1.Status,
test1.AltStatus,
test1.Timestamp,
test1.Duration,
test1.PhoneUsed,
test1.TranslatedPhone,
test1.DialedPhone,
test1.Replies,
test1.CodedReplies,
test1.Volunteer,
test1.AMOGM,
test1.AMType,
test1.NumberNo9s,
test1.CCUsed,
test1.Line,
test2.ContactAttempts,
test2.NumberRepliesReceived,
test2.NumberRejectedConfirms,
test2.PhoneType,
test2.[Order],
test3.FirstName,
test3.LastName,
test3.FullName,
test3.Tagged,
test3.ContactAttempts AS Expr2,
test3.Status AS Expr3,
test3.[Order] AS Expr4
FROM test1 INNER JOIN
PhoneNumber ON test1.PhoneNumberID = test2.PhoneNumberID INNER JOIN
PhonePerson ON test2.PersonID = test3.PersonID INNER JOIN
PhoneJob ON test3.JobID = PhoneJob.JobID WHERE PhoneLog.Timestamp > GETDATE()-3
and test1.PhoneLogID not in
(select PhoneLogID from Database2.dbo.PhoneLog)
I am doing multiple inner joins to get columns from different tables and sending the new records only to the destination database(database2) table. My destination is a OLEDB destination.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 28, 2012 at 11:07 am
My Actual Script(removed test1, test2, test3 names and put the correct ones):
SELECT
PhoneLog.PhoneLogID,
PhoneNumber.PhoneNumberID,
PhonePerson.PersonID,
PhoneJob.JobID,
PhoneLog.Status,
PhoneLog.AltStatus,
PhoneLog.Timestamp,
PhoneLog.Duration,
PhoneLog.PhoneUsed,
PhoneLog.TranslatedPhone,
PhoneLog.DialedPhone,
PhoneLog.Replies,
PhoneLog.CodedReplies,
PhoneLog.Volunteer,
PhoneLog.AMOGM,
PhoneLog.AMType,
PhoneLog.NumberNo9s,
PhoneLog.CCUsed,
PhoneLog.Line,
PhoneNumber.ContactAttempts,
PhoneNumber.NumberRepliesReceived,
PhoneNumber.NumberRejectedConfirms,
PhoneNumber.PhoneType,
PhoneNumber.[Order],
PhonePerson.FirstName,
PhonePerson.LastName,
PhonePerson.FullName,
PhonePerson.Tagged,
PhonePerson.ContactAttempts AS Expr2,
PhonePerson.Status AS Expr3,
PhonePerson.[Order] AS Expr4
FROM PhoneLog INNER JOIN
PhoneNumber ON PhoneLog.PhoneNumberID = PhoneNumber.PhoneNumberID INNER JOIN
PhonePerson ON PhoneNumber.PersonID = PhonePerson.PersonID INNER JOIN
PhoneJob ON PhonePerson.JobID = PhoneJob.JobID WHERE PhoneLog.Timestamp > GETDATE()-3
and PhoneLog.PhoneLogID not in
(select PhoneLogID from PTOnlineWebSiteDB_New.dbo.PhoneLog)
Thanks Again
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 28, 2012 at 12:18 pm
I'm afraid that I don't 100% understand what you are doing. But as you are operating on a single server, I would be tempted to do the whole thing in a stored proc and do away with SSIS completely. If you've already thought about that and decided against it, I would like to hear your reasoning.
But if you decide to continue along the SSIS path, you should do your source selection, and filtering, using the SQL engine - in my opinion. Once you have refined your source selection in this way, you will be able to do a straight source-destination mapping in your package without the need for additional components and it should fly. It will be easier to maintain too.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply