Hello all, I had a slowly changing dimension wich was fed by a flat file followed by some conversions. Now the scenary changed and I have now 4 different flat files to feed it and the dimension has a new column wich value is associated with the file itself, corresponding to the value of the entity as a foreign key to another table for ex: FileA - value of new column -> 1 (corresponding to entityA) FileB - value of new column -> 2 (corresponding to entityB) FileC - value of new column -> 3 (corresponding to entityC) FileD - value of new column -> 4 (corresponding to entityD) My first Idea is to replicate the process 4 times and have the new field hardcoded along with the different filenames, wich is probly the fastest solution, but technically could not be that good. How can I achieve the goal using best practices? Thanks in advance. PS: using SSIS 2005

My first Idea is to replicate the process 4 times and have the new field hardcoded along with the different filenames, wich is probly the fastest solution, but technically could not be that good. Hi Pedro, Since there are multiple sources, so you have to merge them into to one table, either in stage table or in your dimension table. So what is the issue if using four processes? Thanks, Raymond Raymond Li - MSFT

Hello Raymond thanks for your response. I ended up making a foreach cycle with the four importing files and a script task to get the name of the entity from the file to a var used later in a derived column for the insertion. This solved the problem of having to replicate the process. I now have one different problem: I am using a wildcard on the file parameter, I would like to use a string variable with that same wildcard in the file expression so I can parametrize it better, but that does not seem to work. The wildcard is like this "File???*" + @[yearMonth] + ".txt" . the var yearMonth contains a string like 201012. I can only make it work if in the file field, not expression the following File???*201012.txt, but that's not good enough, I need to include the yearMonth var. Am I doing something wrong? How can this be achieved? Thanks in advance.

Hi Pedro, I’m still not sure your issue. The name of physical file contains “???*” or you want to Regular expression for file name? If it’s regular expression, you can refer to: http://msdn.microsoft.com/en-us/library/ms141001.aspx http://msdn.microsoft.com/en-us/library/ms140206.aspx Hope this helps, Raymond Raymond Li - MSFT

Hi All, Pedro if you know in any way the type of flat file from data source (like reading from specific folder or in a built-in field) you can add the column to store the entity type of file in the same dataflow. You can read along folders using a For Each component in Control Flow. In SCD use both columns as Business Keys. Anyway the general recomendation about SCD is dont use it. Instead can implement Lookups and Merge Join transformations with better performance results. Regards.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky

You should create a reference table with the ID of each source and create here the SCD approach. My 2 cents.Visit My Business Intelligence Blog - If your question is answered, please mark as answered.

Yes is a regular expression for the filename concatenated with a variable, in the links or on the net I couldn't find info on how to simulate as a string the DOS wild caracters '?' - meaning any char a least one, or '*' any char even empty in a variable concatenated with another variable the expression is this: "File???*" + @[yearMonth] + ".txt" I really need this to work as an expression containing vars. If I do not use expression on the file and if I use in the textbox of the file the value File???*201012.txt it works, but the number part of the string has to correspond to the content of the variable @[yearMonth]. Thanks in advance

Hi Pedro, Could you check if this is your requirement: “I want to check the files in a specific folder and then pass only validate file ("File???*" + @[yearMonth] + ".txt") to the variable” If yes, you can try this: 1) Create a Foreach Loop Container, switch to Collection tab, select “Foreach File Enumerator” for the property Enumeratro. 2) In Collection tab, specify the Folder, specify the Files. 3) Click button next to the Expression, select the FileSpec under Property column and then use below expression: "File???*" + @[yearMonth] + ".txt" 4) Switch to Variable Mappings tab, and then map the Variable. Hope this helps, Raymond Raymond Li - MSFT