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?
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?
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply