finding filepath, subdirectories, filenames
in my local drive, i have a directory, d:\data\ with unlimited subdirectories liked the following: d:\data\a\a.txt d:\data\a\a2.txt d:\data\a\a3.txt d:\data\b\b.txt d:\data\b\b2.txt d:\data\b\b3.txt d:\data\c\cc.txt d:\data\c\c2.txt d:\data\d\d.txt d:\data\d\d2.txt I created a package with multiple flat file connections and each connection has a Data Flow Task and a Script Compont will look into the assigned connection and find the files, liked: For Each fileName In Directory.GetFiles(Me.Connections.ImportFilesDir.AcquireConnection(Nothing).ToString()) Output0Buffer.AddRow() Output0Buffer.Filename = fileName Next These filenames then will be worked on with Derived column and store into a SQL table. I have got a new requirement to use only one flat file connection to look for the txt files. I was trying to use a table to store each of the path, such as d:\data\a, d:\data\b\, d:\data\c and use ForEach to enumerate them. I don't know how pass the values, such as d:\data\a, d:\data\b, and etc to a DFT which will write the result into the SQL table. Am I taking the correct approach? Is there an example of something liked this on the web I can follow, I am new to this type of SSIS. Thanks. -- IBM has jobs for USA employee to work oversea (India) and get their wages.
January 21st, 2011 10:13pm

Hello, Here are the steps to achive this Step1--Create a variable called VarDirectories , type object in your package Step2: Get the execute sql task , write your query SELECT DirectoryPath from dbo.YOURTABLE , go to result set and select Full result set, then click on the result set tab on left and Add the variable. In variable name put 0 and VarDirectories in Variable. Step3--Get the foreach loop and then click the Collection and then Foreach Ado Enumurator, then select VarDirectory variable in ADO object source variable and then go to variable mapping and create new variable VarDirectoypath. step4-bring another foreach loop inside the foreach loop and then click collection and Select Foreach File Enumurator , then clieck on expression and select directory and pass the value VarDiretorypath. in radio button select the full qualified path and then go to variable mapping and create a new variable VarFullpathDir Step5. get data flow inside inner foreach loop container, bring the flate file source and then oledb destination, create connection for flat file and then map to destination. step6-click on the file connection and go to expressions and connection string and pass VarFullpathDir variable , set Delay validation to True on Data flow task. Note: all the files should have the same metadata for this senario to work You are done. thanks
Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2011 12:24am

Hi, thanks for the deteailed steps. I was able to follow thru for the most part. in step 6, where do i find the file connection? is it in the property area of DataFlowTask? is it in the connection of Flat File? is it in the OLE Destination? or is it somewhere else? "step6-click on the file connection and go to expressions and connection string and pass VarFullpathDir variable , set Delay validation to True on Data flow task." i can't wait to try out with the rest of the code. thanks, again.-- IBM has jobs for USA employee to work oversea (India) and get their wages.
January 24th, 2011 3:55pm

Hello, In data flow you have to create connection by yourself with any sample file. get the flat file connection in data flow task and then create a connection with any file in any folder and then map to the destination table. and then apply step 6, that will overwrite connection string everytime with new file Thanks
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 4:26pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics