Saturday, February 02, 2013 9:29 PMThere 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?
Saturday, February 02, 2013 10:35 PM
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?
Sunday, February 03, 2013 8:34 PMModerator
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
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, February 07, 2013 12:47 PM