Ignore last/corrupted record from flat file source in SSIS

Multi tool use
Multi tool use












0















I have following csv file:



col1, col2, col3
"r1", "r2", "r3"
"r11", "r22", "r33"
"totals","","",


followed by 2 blank lines. The import is failing as there is extra comma at the end of the last data row and most probably will fail because of the extra blank lines at the end.



Can I skip the last row somehow or even better stop import when I get into that row? It always has "totals" string in the "col1".



UPDATE:



As far as I understood from the answers that it is not possible to do that with Flat File. Currently I did that with the "Script Component" as a source










share|improve this question

























  • what version of SSIS (SQL server version)

    – Jayvee
    Sep 15 '17 at 12:21
















0















I have following csv file:



col1, col2, col3
"r1", "r2", "r3"
"r11", "r22", "r33"
"totals","","",


followed by 2 blank lines. The import is failing as there is extra comma at the end of the last data row and most probably will fail because of the extra blank lines at the end.



Can I skip the last row somehow or even better stop import when I get into that row? It always has "totals" string in the "col1".



UPDATE:



As far as I understood from the answers that it is not possible to do that with Flat File. Currently I did that with the "Script Component" as a source










share|improve this question

























  • what version of SSIS (SQL server version)

    – Jayvee
    Sep 15 '17 at 12:21














0












0








0








I have following csv file:



col1, col2, col3
"r1", "r2", "r3"
"r11", "r22", "r33"
"totals","","",


followed by 2 blank lines. The import is failing as there is extra comma at the end of the last data row and most probably will fail because of the extra blank lines at the end.



Can I skip the last row somehow or even better stop import when I get into that row? It always has "totals" string in the "col1".



UPDATE:



As far as I understood from the answers that it is not possible to do that with Flat File. Currently I did that with the "Script Component" as a source










share|improve this question
















I have following csv file:



col1, col2, col3
"r1", "r2", "r3"
"r11", "r22", "r33"
"totals","","",


followed by 2 blank lines. The import is failing as there is extra comma at the end of the last data row and most probably will fail because of the extra blank lines at the end.



Can I skip the last row somehow or even better stop import when I get into that row? It always has "totals" string in the "col1".



UPDATE:



As far as I understood from the answers that it is not possible to do that with Flat File. Currently I did that with the "Script Component" as a source







ssis






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Sep 15 '17 at 13:59







Dmitrij Kultasev

















asked Sep 15 '17 at 8:31









Dmitrij KultasevDmitrij Kultasev

2,82022041




2,82022041













  • what version of SSIS (SQL server version)

    – Jayvee
    Sep 15 '17 at 12:21



















  • what version of SSIS (SQL server version)

    – Jayvee
    Sep 15 '17 at 12:21

















what version of SSIS (SQL server version)

– Jayvee
Sep 15 '17 at 12:21





what version of SSIS (SQL server version)

– Jayvee
Sep 15 '17 at 12:21












3 Answers
3






active

oldest

votes


















2














You can do it by reading the row as a single string.



Conditionally split out Null and left(col0)=="total"



in script component you then use split function



finally trim(""")






share|improve this answer































    1














    I know of nothing built-in to SSIS that lets you ignore the LAST line of a CSV.



    One way to handle this is to precede your dataflow with a script task that uses the FileSystemObject to edit the CSV and remove the last line.






    share|improve this answer































      1














      You will need to create a custom script where you read all lines but the last within SSIS.






      share|improve this answer























        Your Answer






        StackExchange.ifUsing("editor", function () {
        StackExchange.using("externalEditor", function () {
        StackExchange.using("snippets", function () {
        StackExchange.snippets.init();
        });
        });
        }, "code-snippets");

        StackExchange.ready(function() {
        var channelOptions = {
        tags: "".split(" "),
        id: "1"
        };
        initTagRenderer("".split(" "), "".split(" "), channelOptions);

        StackExchange.using("externalEditor", function() {
        // Have to fire editor after snippets, if snippets enabled
        if (StackExchange.settings.snippets.snippetsEnabled) {
        StackExchange.using("snippets", function() {
        createEditor();
        });
        }
        else {
        createEditor();
        }
        });

        function createEditor() {
        StackExchange.prepareEditor({
        heartbeatType: 'answer',
        autoActivateHeartbeat: false,
        convertImagesToLinks: true,
        noModals: true,
        showLowRepImageUploadWarning: true,
        reputationToPostImages: 10,
        bindNavPrevention: true,
        postfix: "",
        imageUploader: {
        brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
        contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
        allowUrls: true
        },
        onDemand: true,
        discardSelector: ".discard-answer"
        ,immediatelyShowMarkdownHelp:true
        });


        }
        });














        draft saved

        draft discarded


















        StackExchange.ready(
        function () {
        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f46235106%2fignore-last-corrupted-record-from-flat-file-source-in-ssis%23new-answer', 'question_page');
        }
        );

        Post as a guest















        Required, but never shown

























        3 Answers
        3






        active

        oldest

        votes








        3 Answers
        3






        active

        oldest

        votes









        active

        oldest

        votes






        active

        oldest

        votes









        2














        You can do it by reading the row as a single string.



        Conditionally split out Null and left(col0)=="total"



        in script component you then use split function



        finally trim(""")






        share|improve this answer




























          2














          You can do it by reading the row as a single string.



          Conditionally split out Null and left(col0)=="total"



          in script component you then use split function



          finally trim(""")






          share|improve this answer


























            2












            2








            2







            You can do it by reading the row as a single string.



            Conditionally split out Null and left(col0)=="total"



            in script component you then use split function



            finally trim(""")






            share|improve this answer













            You can do it by reading the row as a single string.



            Conditionally split out Null and left(col0)=="total"



            in script component you then use split function



            finally trim(""")







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Sep 15 '17 at 20:17









            KeithLKeithL

            2,5881715




            2,5881715

























                1














                I know of nothing built-in to SSIS that lets you ignore the LAST line of a CSV.



                One way to handle this is to precede your dataflow with a script task that uses the FileSystemObject to edit the CSV and remove the last line.






                share|improve this answer




























                  1














                  I know of nothing built-in to SSIS that lets you ignore the LAST line of a CSV.



                  One way to handle this is to precede your dataflow with a script task that uses the FileSystemObject to edit the CSV and remove the last line.






                  share|improve this answer


























                    1












                    1








                    1







                    I know of nothing built-in to SSIS that lets you ignore the LAST line of a CSV.



                    One way to handle this is to precede your dataflow with a script task that uses the FileSystemObject to edit the CSV and remove the last line.






                    share|improve this answer













                    I know of nothing built-in to SSIS that lets you ignore the LAST line of a CSV.



                    One way to handle this is to precede your dataflow with a script task that uses the FileSystemObject to edit the CSV and remove the last line.







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Sep 15 '17 at 13:21









                    Tab AllemanTab Alleman

                    27.1k62442




                    27.1k62442























                        1














                        You will need to create a custom script where you read all lines but the last within SSIS.






                        share|improve this answer




























                          1














                          You will need to create a custom script where you read all lines but the last within SSIS.






                          share|improve this answer


























                            1












                            1








                            1







                            You will need to create a custom script where you read all lines but the last within SSIS.






                            share|improve this answer













                            You will need to create a custom script where you read all lines but the last within SSIS.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Sep 15 '17 at 13:28









                            a415a415

                            119217




                            119217






























                                draft saved

                                draft discarded




















































                                Thanks for contributing an answer to Stack Overflow!


                                • Please be sure to answer the question. Provide details and share your research!

                                But avoid



                                • Asking for help, clarification, or responding to other answers.

                                • Making statements based on opinion; back them up with references or personal experience.


                                To learn more, see our tips on writing great answers.




                                draft saved


                                draft discarded














                                StackExchange.ready(
                                function () {
                                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f46235106%2fignore-last-corrupted-record-from-flat-file-source-in-ssis%23new-answer', 'question_page');
                                }
                                );

                                Post as a guest















                                Required, but never shown





















































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown

































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown







                                ET53AXtO8 1lX 7Upl2yz N7FLweB2wHnN O
                                4sHxfmi,pE0GO nWUrk zmjA8WVy nFwpGrFRg

                                Popular posts from this blog

                                Monofisismo

                                Angular Downloading a file using contenturl with Basic Authentication

                                Olmecas