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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 4,828, Visits: 11,179
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.
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, April 07, 2014 2:09 PM
Points: 79, Visits: 242
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: Today @ 8:58 AM
Points: 1,042, Visits: 1,828
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 4,828, Visits: 11,179
..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.
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: Today @ 8:58 AM
Points: 1,042, Visits: 1,828
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:57 PM
Points: 416, Visits: 512
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: Today @ 3:27 PM
Points: 12,185, Visits: 9,134
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