-1

I have a pretty large file 10GB in Size need to load the records into the DB, I want to have two additional columns

  1. LoadId which is a constant (this indicates the files unique Load number)
  2. ChunkNumber which would indicate the Chunk of the batch size.

So if I have a batch size of 10,000 records I want

  1. LoadId = {GUID}
  2. ChunkNumber = 1

for the next 10,000 records i want

  1. LoadId = {GUID}
  2. ChunkNumber = 2

Is this possible in SSIS? I suppose I can write a custom component for this but there should be a inbuilt ID if i could use as SSIS is already running stuff in batches of size 10,000

Can some one help me to figure out this parameter if it exists and can it be used?

Ok little bit more detail on the background of what and why.

We we get the data into a Slice of 10,000 records then we can start calling the Stored Procedures to enrich the data in chunks, all i am trying to do is can the SSIS help here by putting a Chunk number and a Guid

this helps the stored proc to move the data in chunks, although i could do this after the fact with a row number, Select has to travel through the whole set again and update the chunk numbers. its a double effort. A GUID will represent the the complete dataset and individual chunks are related to it.

Some more insight. There is a WorkingTable we import this large file into and if we start enriching all the data at once the Transaction log would be used up, it is more manageable if we can get the data into chunks, so that Transaction log would not blow up and also we can parallel the enrichment process.

The data moves from De-normalized format normalized format from here. SP is more maintainable in therms of release and management of day today, so any help is appreciated.

or is there an other better way of dealing with this?

bhushanvinay
  • 449
  • 1
  • 5
  • 21

1 Answers1

0

For the LoadID, you could use the SSIS variable

System::ExecutionInstanceGUID

which is generated by SSIS when the package runs.

Mike Feingold
  • 368
  • 1
  • 10