Need faster way to pull oData feed in SSIS

Question

Is there a faster way to grab oData for ssis?

public override void CreateNewOutputRows()
    {
        SomeEntities entities = new SomeEntities(new Uri("https://aurltomystuff.com/mywebservice.svc/"));
        string username = this.Variables.username;
        string domain = this.Variables.domain;
        string password = this.Variables.password;

        entities.Credentials = new NetworkCredential(username,password, domain);

        var tbl = from t in entities.AnEntitySetInMyService
                    select new
                    {
                        AField = t.AField,
                        AField = t.AField,
                        AField = t.AField,
                        AField = t.AField,
                        AField = t.AField,
                        AField = t.AField 

                    };
        int pageSize = 500;
        int recordCount = this.Variables.recordCount;
        int page = 0;
        while (page * pageSize < recordCount)
        {
            if ((page + 1) * pageSize > recordCount) { recordCount = tbl.Count(); }

            foreach (var t in tbl.Skip(page * pageSize).Take(pageSize))
            {
                Output0Buffer.AddRow();
                Output0Buffer.AField = t.AField ;
                Output0Buffer.AField = t.AField ;
                if (t.AField == null) { Output0Buffer.AField_IsNull = true; } else { Output0Buffer.AField = (long)t.AField ; }
                if (t.AField == null) { Output0Buffer.AField_IsNull = true; } else { Output0Buffer.AField = (DateTime)t.AField; }
                Output0Buffer.AField = t.AField;
                Output0Buffer.AField = t.AField;

            }
            page++;
        }

    }

How long does it take to stream the data back (taking SSIS out of the mix)? I'd assume that's your maximum throughput unless your odata provider allows for you to segment your calls (thread 1 pulls rows 1-1M, thread 2 covers 1M-2M, etc) How many rows are you expecting to receive? Your need for speed... what's the challenge you're trying to address? Is it taking too long for the first batch of rows to hit the pipeline (subsequent batches flow fine)? — billinkc, Dec 06 '12 at 01:23

score 1 · Answer 1 · answered Dec 08 '12 at 02:41

I think there is no a way, out of the box, to do it faster. I mean, transformations can be synchronous as your example or, asynchronous. And your implementation is quite standard. If processing requires a synchronous transformation you have no choice however, if it can be asynchronous you can do it as is described in the following article: http://msdn.microsoft.com/en-us/library/ms136133.aspx

I hope it is useful. Good luck!

score 0 · Answer 2 · edited Mar 21 '18 at 15:06

0

Try using the oData source in SSIS. If you're just pulling data, I think it's faster.

edited Mar 21 '18 at 15:06

decates

3,406
1
22
25

answered Mar 21 '18 at 13:55

J.D. Walker

56
6

1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/19183887) – Maciej Mar 21 '18 at 14:19
1

I'm going to respectfully disagree with your assessment. If you read what I wrote with a different perspective, you'll see I *do* provide an answer. I say use the oData source rather than the script because I believe it to be faster. I think your review is unnecessarily splitting hairs. – J.D. Walker Mar 21 '18 at 14:37
1

@J.D.Walker To clarify the reason why the Reviewer added this comment is because your answer sounds more like a comment. I'd recommend you to edit your answer to reword it to sound more like a real answer (e.g. include a small example of how to do this with "oData"). – Filnor Mar 21 '18 at 14:57
1

@J.D.Walker I think the problem lies in phrasing. It **looks** like a chit-chat, not like an answer. Answers are supposed to look like actual answers, not like another question. – Agent_L Mar 21 '18 at 14:59
Correct. Answer may look ok but it's more of a comment - this is how people would normally present it. Good quality answer should have more substance to it. This was a close call though. – Maciej Mar 21 '18 at 22:04

Need faster way to pull oData feed in SSIS

2 Answers2