question

FarhanJamil-5363 avatar image
0 Votes"
FarhanJamil-5363 asked ·

need assistance on some ssis logic

Hi All

I have an ssis package which processes retail sales file. I have absolutely no issue with the package as it is running fine.
The issue i have is

every monday I receive a csv file from the client which has Friday, sat, and Sunday dates. So I have to manually go into excel and split the csv file into three csv sales file .

unfortunately the big file needs to be split into three and then the file needs to be processed one by one
This step is manual. can these be handled in ssis and be automated. so rather than me manually splitting the big file into three with three separate dates i want ssis to do that.

was thinking about this logic

  • conditional split - to check if the date is a monday

  • have three separate variables which will store dates

  • and then have some kind of lookup which will map the three dates with the big file.

would this be a right logic.. if not can anyone of you suggest an easy way of making this simple, if this sounds complicated


Regards
Farhan Jamil



sql-server-integration-services
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

the big file needs to be split into three and then the file needs to be processed one by one

Why to split into 3 files?
What is the problem to process that file as-is?

0 Votes 0 ·

well we have another etl in place which processes the file and then the sales amount is imported into database. this etl is not controlled by us, it is maintained by a third party.

the etl splits it into 200 lines and then the record is inserted into our database.
the issue is when the sales file is let say 10000 lines or more in one csv file the third party etl sometimes skip 200 lines or more during processing and inserting the records into our database.
so we split it into three files so that if lines are skipped then we can manually compare and locate the missing lines and reprocess the files

Unfortunately thats how it is

REgards
Farhan Jamil

0 Votes 0 ·

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @FarhanJamil-5363 ,

was thinking about this logic
conditional split - to check if the date is a monday
have three separate variables which will store dates
and then have some kind of lookup which will map the three dates with the big file.
would this be a right logic.. if not can anyone of you suggest an easy way of making this simple, if this sounds complicated

Yes, it was a reasonable logic.
Could you please share some example data?
Please refer to the following links:
1.SSIS Conditional Split Transformation overview
2.An Overview of the LOOKUP TRANSFORMATION in SSIS

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @FarhanJamil-5363 ,

May I know if you have anything to update?

Best regards,

Mona

0 Votes 0 ·