January 26, 2015 at 11:26 pm
Dear Expert
I have created table having transaction of Jan-2015,
and every day i am getting report from SAP (in excel format to may outlook email id) for till date transaction (i.e. from 01-01-2015 to till date) recorded in Jan month.,
i uploade this exel file in SQL server thru SSIS package ( i delete entire table data and uploade as fresh table)
and all this working fine as per my expectation and result,
but i will have problem when i will be Feb-2015, explaining
from next month i will get Excel report from SAP only for Feb-15 month not from 01st jan to till Feb-15 and if still i use above method then systerm delete entire Jan-15 data and uploade only Feb-15 Data,
now what i want,
when i uploade data systerm should replace only data for Feb-month not for earlier month.
i have seperate column in my table/excel for Month and Year, where Year having "2015" and month having 1,2,3 ect.
i hope i explain well, please help for SQL query which replace data for only current month
January 26, 2015 at 11:48 pm
if you remove the DELETE functionality from your ssis package, data will be appended in your table without any issue.
January 26, 2015 at 11:55 pm
if you remove the DELETE functionality from your ssis package, data will be appended in your table without any issue.
Dear Sir, thanks for reply
but if i remove delete function then my data will be repeate every time and i will not get accurate data,
in need to replace old data and uploade new data with some additional transaction,
in my SSIS pacage i had selected "Delete rows in destination table"
can i filter here ?
January 27, 2015 at 12:11 am
Is there a unique key in the source Excel data?
It seems that your ideal flow would be something like:
1) Process Excel file. For each row:
2) Does row exist in target table?
a) Yes: update existing row
b) No: append row to target table
Is that right?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 27, 2015 at 12:14 am
I think you need to share more details regarding your package.
As per my understanding, if you have Jan-2014 data in a table. And now want to upload the data of Feb-2014.
how can data will be duplicated in this case ?
January 27, 2015 at 12:19 am
For huge amount of data :-
Load Excel data into Intermediate table (create this) and then do MERGE with primary table if they( Primary & Intermediate) have any joining Columns...
For Less Amont of Data:
Use Lookup Transformation
January 27, 2015 at 12:34 am
Is there a unique key in the source Excel data?
It seems that your ideal flow would be something like:
1) Process Excel file. For each row:
2) Does row exist in target table?
a) Yes: update existing row
b) No: append row to target table
Is that right?
Mr. Phil Parkin
no sir there is no any unique ket in my source excel data.
but my process flow like, e.g. i am in Feb-15, now my sql table having only Jan-15 data, now i want to upload Feb-15 data and flow as
1) Process excel file. for each row: (excel having data where Month = 2)
2) append the table without deleting Jan-15 Data (on first day of Feb15)
3) append the table without deleting Jan-15 Data but replace all Feb-15 data and uploade excel file (one second day of Feb-15)
January 27, 2015 at 3:12 am
atul.jadhav (1/27/2015)
Is there a unique key in the source Excel data?It seems that your ideal flow would be something like:
1) Process Excel file. For each row:
2) Does row exist in target table?
a) Yes: update existing row
b) No: append row to target table
Is that right?
Mr. Phil Parkin
no sir there is no any unique ket in my source excel data.
but my process flow like, e.g. i am in Feb-15, now my sql table having only Jan-15 data, now i want to upload Feb-15 data and flow as
1) Process excel file. for each row: (excel having data where Month = 2)
2) append the table without deleting Jan-15 Data (on first day of Feb15)
3) append the table without deleting Jan-15 Data but replace all Feb-15 data and uploade excel file (one second day of Feb-15)
No unique key? I don't like the sound of that, I'll admit.
Your options are limited. Something like
1) Read Excel file, determine month and year.
2) Delete data for that month and year (only) from the target table.
3) Load data into target table.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 27, 2015 at 3:31 am
Dear Phil Parkin
yes, that's why I created table and have separate Year and Month column in my excel/sql table
please give way
January 27, 2015 at 3:53 am
atul.jadhav (1/27/2015)
Dear Phil Parkinyes, that's why I created table and have separate Year and Month column in my excel/sql table
please give way
I told you the way. I'm not going to write it for you! 😀
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 27, 2015 at 4:11 am
ok, what will be the way if I created unique key in my data ?
I will join "Year&Month&CustomerAccount" and create unique key
then what will be the solution
January 27, 2015 at 4:23 am
Ok , then what will the solution if I created unique key
I will join "Year&Month&CustomerNo." and generate unique key
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply