Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS check if file was already loaded ? Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 12:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 4:32 PM
Points: 63, Visits: 116
I want to make a package to load some text files into a database table. Any suggestions on how I can check if a file has been loaded already or if a duplicate file exists, then don't load the duplicate ?

Thanks.
Post #1499002
Posted Thursday, September 26, 2013 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 5,317, Visits: 12,362
etl.laptop (9/26/2013)
I want to make a package to load some text files into a database table. Any suggestions on how I can check if a file has been loaded already or if a duplicate file exists, then don't load the duplicate ?

Thanks.


After loading a file, move it to an archive folder (and rename it to include the load date in the file name). Then you can't load it twice.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1499027
Posted Saturday, September 28, 2013 2:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, Visits: 243
You can use either of the approach to solve this:

1. Use a For each Loop container to load the text files and then move them to archive or delete them.
2. If you have a business key in the table and text file, you can compare the business key in text file with table to validate that file has been previously loaded or not.
3. Duplicate file will not have same name but they may have same data. Use approach #2.


Vikash Kumar Singh || www.singhvikash.in
Post #1499656
Posted Monday, September 30, 2013 12:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 3:10 AM
Points: 1,047, Visits: 1,852
For the #2 approach which you have mentioned, the design must have a dedicated staging area tables to perform an OUTER JOIN with destinations to load only new data.


Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1499850
Posted Monday, September 30, 2013 12:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 5,317, Visits: 12,362
..must have a dedicated staging area tables ...


Please justify this comment. Because I can think of a way of doing this without staging tables.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1499861
Posted Monday, September 30, 2013 12:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 3:10 AM
Points: 1,047, Visits: 1,852
MERGE JOIN transformation?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1499862
Posted Monday, September 30, 2013 1:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 12:53 AM
Points: 525, Visits: 648
i believe the best solution will be of using the an archived folder where u can move the processed files. (use file system task )
Post #1499865
Posted Monday, September 30, 2013 1:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 6:46 AM
Points: 13,646, Visits: 11,524
Mr. Kapsicum (9/30/2013)
i believe the best solution will be of using the an archived folder where u can move the processed files. (use file system task )


+1

It's a system hard to break + you have the loaded files waiting for you in a directory for error handling or auditing.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1499869
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse