February 25, 2013 at 5:44 am
I've been tasked in creating an ssis solution which extracts data once a day, loads specific fields and exports them to a .txt file (pipe delimited), As the final step, it uploads the file via FTP to a source. I've accomplished all these steps successfully, however, I was just asked if the file uploaded could be cumulative since it's uploaded daily,and this is where I'm drawing a blank to fullfill this request. I'm not sure what I need to do additionally to finalize this solution with the cumulative uploads. Any light shed will be appreciated, as always.
Thanks,
John E
February 25, 2013 at 5:58 am
latingntlman (2/25/2013)
I've been tasked in creating an ssis solution which extracts data once a day, loads specific fields and exports them to a .txt file (pipe delimited), As the final step, it uploads the file via FTP to a source. I've accomplished all these steps successfully, however, I was just asked if the file uploaded could be cumulative since it's uploaded daily,and this is where I'm drawing a blank to fullfill this request. I'm not sure what I need to do additionally to finalize this solution with the cumulative uploads. Any light shed will be appreciated, as always.Thanks,
John E
Can you be a bit more specific about what 'cumulative' means in this case?
Is the problem that you are doing a full extract every day and the requirement is for changes only?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 25, 2013 at 6:43 am
I'm sorry I wasn't more specific. It could be either a cumulative file daily or a file with just new records/leads.
regards,
John
February 25, 2013 at 7:32 am
OK, I'm not getting any closer to understanding what you want.
Can you provide some sample data showing what you currently have and what you want to have?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 25, 2013 at 8:39 am
quick update:
At this point I just need to append a date time stamp to the name of the file (i.e. FileName_YYYYMMdd_HHmmss.txt)
How can I accomplish this?
Regards,
John
February 25, 2013 at 8:56 am
latingntlman (2/25/2013)
quick update:At this point I just need to append a date time stamp to the name of the file (i.e. FileName_YYYYMMdd_HHmmss.txt)
How can I accomplish this?
1. Set a string variable with an expression for the file name. The following is close but the date is not formatted to your specifications and will need a bit more work.
"FileName_" + (DT_WSTR, 50) (DT_DBTIMESTAMP)GetDate()
2. Set the connection string expression to the variable in the connection manager for the text file. Be sure the path to the file is included.
February 27, 2013 at 9:10 am
Done, but I obviously had to modify the expression but it works. Now, I need to send an email upon successful FTP task with the record count from the .TXT file, but I'm not sure if I need to do this thru a data flow task or control flow task. How do I best accomplish this?
Thx,
John
February 27, 2013 at 9:19 am
latingntlman (2/27/2013)
Now, I need to send an email upon successful FTP task with the record count from the .TXT file, but I'm not sure if I need to do this thru a data flow task or control flow task. How do I best accomplish this?
Check out the 'Row Count' Transformation in BOL or MSDN. Here's the article from MSDN.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply