Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Dynamic splitting of columns while loading the data to flat file in ssis

Answered Dynamic splitting of columns while loading the data to flat file in ssis

  • Saturday, February 02, 2013 9:29 PM
     
     
    Sign In to Vote
    0
    There are 10 to 30 columns in a table(Used dynamic pivoting to load to that table).So based on number of columns generated in to that table,load the first 10 rows to the flat file and next 10 rows as another row in the same flat file and next 10 rows as another row in the same flat file and so on.Is there any way to do that?
     

All Replies

  • Saturday, February 02, 2013 10:35 PM
     
     
    Sign In to Vote
    0

    Hi ,

    If I understand correctly you are trying to load data from a table (10 to 30 col.) into FF. And you are trying to load 10 rows at a time from table to FF? Yes, it is possible if you have a ID int col. in table. So first time you load 10 rows with your package. Next run, it will load next 10 rows into FF and so on..Is this the way you want it?

    Thanks, hsbal

     
  • Sunday, February 03, 2013 8:34 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    If the source table gets generated on the fly (variable number of columns) then the package metadata changes from a run to run and this will make your package break. SSIS relies to static metadata to work after all.

    In such situations creating packages dynamically becomes necessary.

    To load only a pre-set number of rows you simply construct a source with the proper SQL (I doubt 10 is a hard-coded value as well as two files always), so if you start bringing a variable number or a value picked from config how many rows get outputted and / or files you produce then making the package created dynamically makes even more sense.

    Not sure what you mean about "based on number of columns load first 10 rows" - looks like you are going to count the columns, this is another +1 for using the dynamic method.

    PS: Apart from using the pure .Net you can resort to using EzAPI interface to produce the package which may be very suitable in your scenario.

    Arthur My Blog