Rharve


Hello,

I have a huge table of around 6.2 billion rows in Oracle and I need to load this table to SQL Server. I am creating multiple raw files based on contact_dates in the table. I have used conditional expressions to branch out and create raw files. I need to specify static file names for each branch or I have to create multiple variables for each branch and use expressions to create file names.

My intention is to create only 1 variable and evaluate the expression based on the conditional expression description. Is this possible

Regards

RH




Re: Dynamic File Names for Raw files created by conditional expressions

Phil Brammer


Here's one way to do it, perhaps:

Create an execute sql task in the control flow to select distinct contact_dates from your table accordingly. Store the results in an object-typed variable and then use a foreach-loop to spin through each date in that variable. Then, you'd put a data flow inside the foreach loop, using the current contact_date variable (populated from the foreach loop) as the destination file name. Of course, you'd have to select from the Oracle table using the contact_date in your where clause, but if your table is partitioned and/or indexed, this might not be a problem.






Re: Dynamic File Names for Raw files created by conditional expressions

Rharve

Thanks for the info. I will try implementing your solution.

Regards

RH






Re: Dynamic File Names for Raw files created by conditional expressions

Phil Brammer

At 6.2 billion rows, my solution will likely be slow, but if you are archiving this table or something and can afford to just let the package run, it should work. Stop back in if you need help in getting things hooked up and working correctly.





Re: Dynamic File Names for Raw files created by conditional expressions

Rharve

You are correct. I have been running this SQL for some time now, and I can't afford to wait this long and I cannot let the package run. This package is not for archival purposes as we are migrating from oracle to SQL Server.

I may have to hardcode the filenames to each of the raw file.

Regards

RH