November 7, 2011 at 2:25 pm
I need to repeatedly put hundreds id on this template excel file. It is time Consuming to make hundreds id repeat the same on excel. Is there any efficient way to dynamically input id?
time term test name id
1 1 0 RTHF
2 2 0 RTHF
3 3 0 RTHF
4 10 0 RTHF
5 11 0 RTHF
7 7 0 RTHF
8 8 0 RTHF
9 8 0 RTHF
10 11 0 RTHF
11 11 0 RTHF
12 11 0 RTHF
My hundreds id are not sequential. They are just random number on separate excel files.
id = 12, 32, 41 .......55,76,123,456
They have to been individually put on the template excel file and time, term, test and name on template no change. For example, the result will be like this:
time term test name id
1 1 0 RTHF 12
22 0 RTHF12
3 3 0 RTHF12
4 100 RTHF12
5 11 0 RTHF12
7 7 0 RTHF12
8 8 0 RTHF12
9 8 0 RTHF12
10 11 0 RTHF12
11 11 0 RTHF12
12 11 0 RTHF12
1 1 0 RTHF 32
22 0 RTHF32
3 3 0 RTHF32
4 100 RTHF32
5 11 0 RTHF32
7 7 0 RTHF32
8 8 0 RTHF32
9 8 0 RTHF32
10 11 0 RTHF32
11 11 0 RTHF32
12 11 0 RTHF32
1 1 0 RTHF 41
22 0 RTHF41
3 3 0 RTHF41
4 100 RTHF41
5 11 0 RTHF41
7 7 0 RTHF41
8 8 0 RTHF41
9 8 0 RTHF41
10 11 0 RTHF41
11 11 0 RTHF41
12 11 0 RTHF41
November 7, 2011 at 11:44 pm
My first guess:
get a dataflow inside a for (each) loop. Loop as many times as you need to generate output Excel files (so I guess hundreds?)
Inside the dataflow, read the template file and add a script component as a transformation. Inside the script component, call a random number - I'm sure .NET has specific functions to do this - and add this number to the rows.
Write the result to an Excel file. Put an expression on the Excel connection manager, so that the destination filename changes for each iteration of the loop.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 13, 2011 at 5:55 pm
You can add a Data Flow Task in your package.
Inside Data Flow Task add a Excel Source to read all the columns.
Add a Script Component task as Transformation. Inside the Script Component for each of the row used Random function of .NET to put some random values in ID column.
In the third step add a OLE DB Destination or Excel Destination to put back all the rows withe randomly generated IDs.
_____________________
Vikash Kumar Singh
Vikash Kumar Singh || www.singhvikash.in
November 14, 2011 at 8:12 pm
agreed, a while loop in query window will do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply