December 7, 2009 at 2:23 pm
I have a SSIS that insert data into SQL tables. The flat file will be taken from a specific path, but the file name will change, it all depends about the date. So I do have a connection to read the file and in its Expression Builder I have this instruction that help me to select the next file:
"\\\\192.168.1.31\\e$\\FILE-"
+ (DT_WSTR,4)YEAR(dateadd("dd", -1, GETDATE()))
+ RIGHT("0" + (DT_WSTR,2)MONTH(dateadd("dd", -1, GETDATE())), 2)
+ RIGHT("0" + (DT_WSTR,2) DAY(dateadd("dd", -1, GETDATE())) , 2)
+ ".csv"
expression:
\\192.168.1.31\e$\FILE-20091206.csv
My process is working fine; it reads the file and insert the data into the table.
I have 2 new requests to add to this SSIS.
a) Read a second file that is coming from different path (same server) and process the same. I mean, we will insert 2 files instead 1.
b) If the insertion into the tables was succeeded, I need to insert into a table a log of the file processed.
How I can for option:
a)To repeat the process for a different file?
b)To pass the filename into a variable and if the process is succeeded insert a log into a table. The log has to be:
a.FlatFile Name
b.Date Created
c.Hash of the file
d.Processed (1 or 0)
Any advices?
December 7, 2009 at 2:32 pm
You could use a For Each Next loop, and have a recordset that lists the files you want to import.
Then for the other part, have a second step that does your logging, and put a success constraint on the flow from the prior step. (Right-click the arrow between the steps and modify the properties on it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 7, 2009 at 2:52 pm
I cant use a Loop becasue the path where I need toread has more than 1000 files, then the loop will process all the files. I need to repeat the process but reading the right file. the file will depende about the date.
December 7, 2009 at 2:56 pm
Doesn't have to be a For Each File loop. Can you create a view that will select the file name (and path) for the two files you want? If so, then you can feed the loop with the dataset from that view.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 9, 2009 at 12:30 pm
I can’t create a view at this point. Flat Files are located in specific path in another server. Maybe I’m not clear about the process.
I have 3 connections in my SSIS.
One connection is to a database where I should insert the data.
Second connection is to read the drive in another server where the files are located.
Third connection read the file and it has and Expression String value to read the next file to be processed
"\\\\192.168.1.31\\e$\\FILE-"
+ (DT_WSTR,4)YEAR(dateadd("dd", -1, GETDATE()))
+ RIGHT("0" + (DT_WSTR,2)MONTH(dateadd("dd", -1, GETDATE())), 2)
+ RIGHT("0" + (DT_WSTR,2) DAY(dateadd("dd", -1, GETDATE())) , 2)
+ ".csv"
expression:
\\192.168.1.31\e$\FILE-20091206.csv
First and second are not the problems. Third connection settings need to be modified because the path of the file will change. I tried to change the Expression that I wrote before for a user variable, but I don’t know how to indicate the files to be read in that variable. If I used the Loop, I honestly don’t know how to indicate the files that it need to read to process.
Any idea?
December 9, 2009 at 2:25 pm
Put the result of the expression into a variable, read the variable at the start of your loop - or create a second variable, and put the result of the expression into that variable & just duplicate your read/write sequence.
December 10, 2009 at 7:47 am
Ok, I got this so far.
I can read my files coming from different folders. You have to check the Traverse subfolders option on the collection in the Editor of the Foreach Loop Editor. Check this link
http://www.windows-tech.info/15/393a376c8c56c5da.php
I'm still dont know where to put the expression to take the file that I need. I put the expression in the expression option of the loop, but still read the more of 100 files on the folders.. Any idea where to pass the filename value ?
Regards,
December 10, 2009 at 8:12 am
I GOT THE SOLUTION:
If you are looking to insert data into a database from flat files. Those flat files are located in different paths and you only need to get the file of the day before. This is what you have to do:
1) Create a ForEach Loop to read the files
2) Edit the ForEac Loop
3) in the Collection options select in folder the path where the files are located
4) in Files put the xtension's files to be processed
5) check the Traverse Subfolders --> This one will read the different folders/files taking as a reference what you put in number 3
6) In Expression add a FileNameRetrievel Property and add the value of the file to be read. (In this case the expression that I post first). This one will get the name of the file to be processed. In my case the names are the same in both folders.
7) Select the option "Name and Extension"
8) Pass the dynamic value into a variable. Select Variable Mappings and Select your variable (previous added ofcourse) and mark index as 0.
9) Pass the variable in the Expression Value in your flatFile Connection.
I hope this can help someone 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply