April 19, 2011 at 10:34 am
Have a business rule, where it has to load file in order. i will try to explain what exactly i am doing
In Forloop:
step 1 : checking if file exists in table(xxyy) and setting up a variable1 with boolean value (say, if the file exists then the variable1 assigned to TRUE)
Step2 : loading data from file to staging table.( only files that doesn't exists in table (xxyy))
Step3: Loading table (xxyy) with file name (as it is processed)
Step4: Archiving file.
Couldn't figure out very exactly i can accomodate the business rule in my above mentioned logic. I get files in particular format with numbers at end (abcd854.1) as soon as this file loads up, the very next file(abcd854.2) should load up. If there is no file the logic should end and no processing of file should take place.
Sometimes, there may be a case where files are not sent in order. some one can help me out ..appriciate it
April 20, 2011 at 12:28 am
You could get all the files from the folder using a script task (use DirectoryInfo). Store these in a SQL table.
Retrieve them again using a SQL SELECT statement with an ORDER BY clause. Loop over the resultset with a for each loop and import each file inside the loop.
(it is probably possible to create a recordset inside the script task and store this right away in a SSIS variable of type Object, but my .NET skills aren't that strong. But with this approach, you'd skip the SQL Server part).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2011 at 8:45 am
but how can my program figure it, if there is next avaliable file (say a.2)... And more over in some cases i may get files without order(a.1,a.2,a.4,a.6) Here i should load a.1 and a.2, as a.3 is not there i should make sure the package should stop. This is Rule
You say, to load all the files in a Table but what is the use if the rule is not met...i don't think this can help me.
April 20, 2011 at 11:34 pm
You should have explained "the rule" a bit better in your original question.
Allright, when I said store the files in a SQL table, I meant the filenames.
So you have all the filenames of the source folder in a table.
With TSQL, you can figure out which filenames to import, by crosschecking with table(xxyy). Use an ORDER BY at the final select to force the order.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 21, 2011 at 1:23 pm
Thank you for the reply...
Are you saying to load all the flat files names to Table and then compare it with xxyy table and then (if not matched) Load up to Staging table right??
But i want to make sure, the files in the folder (Flat files) needs to be picked up in order.
Ex: (a.1, a.2,a.4) this are the group of Flatfiles in my Source Folder
1. Now i check with xxyy table and then load them into staging table.
But according to your logic, it loads up all the files in the Folder. Which it should not, because the a.3 file is missing from the folder hence the package should stop and not execute futher untill the a.3 flatfile loads up. Plz do write me, if this is not clear
April 21, 2011 at 11:27 pm
Load all the filenames into a staging table, so you can use TSQL to validate it.
(or write a script task in SSIS that uses .NET to validate, whatever floats your boat).
With validation, I mean:
* check if files are missing
* check for sequence gaps
* force an order
* ...
After these checks, you can select the files you actually want to upload.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply