Import new data from new database job should run daily few times

motioneye
motioneye used Ask the Experts™
on
Hi,
I'm in the midst of creating ssism as per screeshot I have created the siurce and destination but how to have the T-sql command where I can write T-sql to copy only certain data / data which not exist in destination db for inserting ?
ssis.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
to write SQL command, look at the attached screen shot.

ee.JPG

Author

Commented:
Hi,
I'm trying with T-sql command windows, but then I lost the table mapping :(
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I can't click oK as it ask for the mapping in lookup component too
Untitled.jpg
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
you need to map the Source and destination columns, so that Ok button would appear..

Author

Commented:
Hi,
Ok successful, but again how do I save this packages in sql server and run it daily with sql jobs schedule

Author

Commented:
Oh,
It encountered me some error while doing debugging, how can we avoid this such errors?

Information: 0x400490F5 at Data Flow Task, Lookup [542]: component "Lookup" (542) has cached a total of 614783 rows.
Warning: 0x802090E4 at Data Flow Task, Lookup [542]: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.
well try to setup LOOKUP task as described in my article.

http://www.sqlhub.com/2010/07/lookup-transformation-task-in-ssis-2008.html

Author

Commented:
Hi Riteshah,
I want regardless of duplicate or not data must be inserted into destination table, can this be done ?
do you want to transfer all data everytime? have you tried the example I saw in my blog? I have not used CACHE.

Author

Commented:
No haven't tried as yet..
actually my data could have duplicate since I'm using datetime with getting values from getdate(), that is why our table have no PK or unique indexes, actually when we not used cache will the ssis transfer all data although its duplicate ?
no matter you use CACHE or not,  LOOKUP will transfer either match data or no match data based on the condition you set.. try to read and understand the article first, it will make you concept clear.

Author

Commented:
I look at lookup transformation editor, I couldn't found general page? oh btw I'm using ssis 2008
try.jpg

Author

Commented:
half way solved my issues

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial