none
SSIS to MS SQL Server (Passing 1 row at a time)

    Dotaz

  • Hey fellow SSIS mates,

    I am having trouble in passing 1 row of data from SSIS to MS SQL Server. The data type which was imported to SSIS is of csv format and I was successful in passing through the whole data set at once but not been able to find a solution on passing 1 row at a time. Your help will be much appreciated.

    Thanks!

    čtvrtek 12. července 2018 22:46

Všechny reakce

  • Hey fellow SSIS mates,

    I am having trouble in passing 1 row of data from SSIS to MS SQL Server. The data type which was imported to SSIS is of csv format and I was successful in passing through the whole data set at once but not been able to find a solution on passing 1 row at a time. Your help will be much appreciated.

    Thanks!

    čtvrtek 12. července 2018 22:47
  • Hi AMRUTDESHPANDE05,

    Have a try following options:

    1. If your source comes form relational database, you can change your query to only fetch 1 row, for example T-SQL statement looks like:

    SELECT TOP 1 * FROM TableName where clause

    2. Using Row Sampling Transformation to limit only one row to output.

    3. If your data has identity column that you can give a condition to filter the data with Derived Column/Condition Spilt Transformation.

    Regards,

    Pirlo Zhang  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    pátek 13. července 2018 2:51
    Moderátor
  • Thank you so much for your response Pirlo, Much appreciated. What if my source file is a excel file?(csv or xlxs)? how do i transfer 1 row to database from a local excel file source?

    Your response will matter alot. Thanks

    Best regards,

    -Amrut

    čtvrtek 19. července 2018 18:20
  • Use OLE DB Command task in DFT

    OR Script Component


    mohammad waheed

    čtvrtek 19. července 2018 18:27
  • Thank you so much for your response Pirlo, Much appreciated. What if my source file is a excel file?(csv or xlxs)? how do i transfer 1 row to database from a local excel file source?

    Your response will matter alot. Thanks

    Best regards,

    -Amrut

    Can I ask the need for this first?

    Looks like a bad approach to me. You should be trying to use batch processing which is what SQL is optimized for. Doing it in row by row approach would be very slow

    Regardless of whether its Flat file source or DB you can use row by row iteration. But it should only be the last resort when no other option exists. Hence trying to find out your exact use case.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    čtvrtek 19. července 2018 18:31
  • Thanks for the response Visakh.

    My use case is a agricultural data of which i need to do predictive measurement. So I have a failure in my agricultural setup and i need to predict it to avoid. so passing 1 row at a time gives me a chance to have understanding of historical data before it fails.

    but what i intend to ask is, can't i pass 1 row from excel file at a time? 

    let me give an example:

    Excel csv file has 10 variables and 100 rows.

    ssis package:

    should pass 1 row of 100 rows at a time to database

    sql server: 

    the database should get updated 1 row at a time

    I hope it answers your question. And if you can now try to give a solution to my problem then it will be great!

    Thanks,

    Amrut Deshpande

    čtvrtek 19. července 2018 21:13
  • hi Amrut,

    Do you want to load only one row from the source or you want to load all the rows but one-by-one. If it is the former, then you just need to find a way to select only that particular row based on some condition. If it is the later case, what is the business case for loading this way? As Visakh16 mentioned this approach is as bad as using cursors in SQL programming.

    If however, you do want to do this deliberately, you can do this using ForEach loop task. Take reference from this post: http://blog.pragmaticworks.com/looping-through-a-result-set-with-the-foreach-loop


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.


    čtvrtek 19. července 2018 21:35