So once I was working with my usual SSIS packages and this time the source specified was fetching data from MDX. During the testing phase, everything went well and the staging ETL went smoothly as expected. An SSMS job was created for calling this package everyday.
After a few days, we realized there was something wrong with the data.The data populated in the stage table was very selected. For example if the MDX specified 3 years worth data, then only the data for 2 years would get populated. This was quite a mystery for us knowing that the job was running as scheduled and the MDX was bringing data once run separately. After some rnd, we found the following as our solution :-
Previously, I had simply used "OpenRowset" which was truncating some data. Changing this to "OpenRowset with Fastload" solved everything and I haven't faced that problem ever since.
After a few days, we realized there was something wrong with the data.The data populated in the stage table was very selected. For example if the MDX specified 3 years worth data, then only the data for 2 years would get populated. This was quite a mystery for us knowing that the job was running as scheduled and the MDX was bringing data once run separately. After some rnd, we found the following as our solution :-
Great Post. Quick and very informative.
ReplyDeleteThank you, sir :)
DeleteThanku dear for this amazeballs blog very much appreciated indeed. Very relative info for ma future endeavours. This has nt only greatly excited me but also made me hungry. Yes hungry. May jesus bless u ma child for this glorious blog. Im like a dog with two tails right now. Impressed by my idiom? I thought so. Anyhow love this lovely blog ma dearie clapping like a seal. Kasmay. Rab rakha child
ReplyDelete