[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SSIS Newbie:  how can I get two data streams from an OLEDB datasource.

Posted on 2011-02-10
5
Medium Priority
?
386 Views
Last Modified: 2012-05-11
Hi All,

I've just created my first SSIS package.  Right now it just consists of a Data Flow task which has inside of it a OLEDB Source task with my sql query, and a Flat File destination which outputs one column of the data to a flat file.  Back up on the Control Flow level I also have a File System task which moves the flat file to another location.  

What has me stumped is how I achieve my desired next steps.  Basically I need to build in the ability to remove any duplicates (items that have already been put into the flat file) before the flat file is created.  I have a plan, I just don't know how to get there.  Here is what I'm thinking:  Initially, just on the first run, run two separate but identical OLEDB sources, one to create my flat file and one to go to a separate flat file that I'll use as my 'archive" and input for the duplicate check.  As far as the actual duplicate check, I have an example I can follow for this so I'm not too concerned there.  Once the duplicate check is complete I'm hoping to be able to append any new rows to the end of this "archive" flat file which I'm using as input for the duplicate check.

So, I guess my question is - does this approach make sense?  And also, will it be possible to append rows to the archive file as I am hoping?
0
Comment
Question by:brl8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 34871384
This is what you can do to check the duplicates

--Create a Staging table
--load the Data from the Archive file into a Temp Table (Staging table)
--now in the Current Data Flow Task you have, Add a Lookup Transformation and Lookup the Values in the Staging Table
--Input the Non Matching rows from Lookup to your Archive file

Staging table
http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Lookup
http://www.sqlshare.com/ssis-lookup-transform-basics_632.aspx

hope this Helps!!
0
 
LVL 1

Author Comment

by:brl8
ID: 34889001
Thanks, vdr1620.

I read through what you sent.  Maybe this is a stupid question but is the temp table deleted after the package completes?  If so, this will not work as I need to keep a running record of any record I've already output into the flat file and only bring over new records.

Thanks,
brl
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 34889174
Yes, It will be deleted at the End of the Package completion... I am not sure why you think it will not work.. because you will be loading all the data into the Temp table each time the package is Executed and that way you will be able to check the existing records... you will need to use a date Column if one exists to get the incremental data

If you still think that the above approach will not work..then i am afraid to say that the only other solution would be to create a table in the database and update the values on each run or store the Value in a file and update the values in the file on each run

will the existing data in the Table Change after inserting it into the Flat File ??
0
 
LVL 1

Accepted Solution

by:
brl8 earned 0 total points
ID: 34929743
vdr1620 - thanks for your replies.  I think your method would work.  But, my preference is not to create a database table.  Also, I need the data to persist until the next time I generate the file (I need to know which are the new records.)  I don't need to worry about the existing data changing, I just need to get new records (I think I did not mention this in my original post).

I figured out a way to do this.  I am creating an import file, then putting the results of the SQL query into a text file.  This text file represents the state of the database table at the moment the import file is created.  That way, the next time I run the import, I join the text file to the new results from the query, filtering out anything where there is a match.  Then, I recreate the text file.  This is working perfectly for me.  
0
 
LVL 1

Author Closing Comment

by:brl8
ID: 35356908
I figured out a way to do this that is working for me.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question