I currently have dynamcialy created PDF's created through a script task in C# that someone else created. They currently have names like Ford-8-7-2012, Nissan-8-7-2012. The date part is based off of the date it is run. I need to change the file names to the previous month and current year, which is what the data is for. With the names above the new file names would be Ford-7-2012, Nissan-7-2012. I am not sure if I can use an expression or if I have to dig through the C# code to fix this, as I am not very handy in C#. I also didn't know if there would be problems when Jaunuary comes considering just reenaming the month aspect would cause it to carry over the previous month with the current year so this coming New Year would be Ford-12-2013 instead of Ford-12-2012. Any suggestions?

If its a one time shot then better use a console application to write up a short piece code and use it.Please mark the post as answered if it answers your question

I currently have dynamcialy created PDF's created through a script task in C# that someone else created. They currently have names like Ford-8-7-2012, Nissan-8-7-2012. The date part is based off of the date it is run. I need to change the file names to the previous month and current year, which is what the data is for. With the names above the new file names would be Ford-7-2012, Nissan-7-2012. I am not sure if I can use an expression or if I have to dig through the C# code to fix this, as I am not very handy in C#. I also didn't know if there would be problems when Jaunuary comes considering just reenaming the month aspect would cause it to carry over the previous month with the current year so this coming New Year would be Ford-12-2013 instead of Ford-12-2012. Any suggestions? Is that a file rename operation? There is a possibility to rename files in a ForEach loop. See this: http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html You may need to add logic in between to probe for validity of renaming a given file. Arthur My Blog

it is a monthly process that needs to be scheduled

The link shared by ArthurZ is what you require. There are a couple of tweaks that you might have to do so I would just list down the summary of what you have to do (I am going to use script task instead of expression from the link as this is a bit cleaner to explain): Here are the steps you can do to get the latest file from the source folder and process it: 1 - Add 2 variables "@ActualFileName" string, "@NewFileName" string to the package. 2 - Add a "Foreach Container" to get the list files from the source folder 3 - Within that "Foreach Container", add a "Script Task". 4 - Pass the 2 variables to the script task. 5 - In this script task, split the file name from @ActualFileName and set the new format to @NewFileName. the pseudo code will be: var <filename> = <get file name from @ActualFileName> var <date> = <build date from filename> SET @NewFileName= <build new File name from filename and date> 6 -Save the script task 7 - Next to the script task add a File System task and set it properly with the source name as @ActualFileName and new name as @NewFileName to rename the file. hope this helps-Please mark the post as answered if it answers your question

The link shared by ArthurZ is what you require. There are a couple of tweaks that you might have to do so I would just list down the summary of what you have to do (I am going to use script task instead of expression from the link as this is a bit cleaner to explain): Here are the steps you can do to get the latest file from the source folder and process it: 1 - Add 2 variables "@ActualFileName" string, "@NewFileName" string to the package. 2 - Add a "Foreach Container" to get the list files from the source folder 3 - Within that "Foreach Container", add a "Script Task". 4 - Pass the 2 variables to the script task. 5 - In this script task, split the file name from @ActualFileName and set the new format to @NewFileName. the pseudo code will be: var <filename> = <get file name from @ActualFileName> var <date> = <build date from filename> SET @NewFileName= <build new File name from filename and date> 6 -Save the script task 7 - Next to the script task add a File System task and set it properly with the source name as @ActualFileName and new name as @NewFileName to rename the file. hope this helps-Please mark the post as answered if it answers your question

Thank you for your help but I am not doing something correct in the script I continue to recieve an error. I have looked into the link above but it also does not work. I should add that after the pdf is created, I use a script task in a foreachloop with the following code: Dts.Variables["User::MyFiles"].Value = Dts.Variables["User::MyFiles"].Value.ToString() + "|" + Dts.Variables["User::MyFile"].Value.ToString(); Then a send mail task to email out all of the files in the folder. I am not sure if this code is similar to what your code is. I need to just rename all the files in the folder, then send out all the files, then after move them. I cannot use the link above as I am trying to just rename the files so I can send them out with the correct name, then move them to a archive folder after. I have tried using the link above but I also recieve errors, like "The Source Path is Incorrect" and I think I am doing the entire script task incorrect because I recieve and error that says the binary code does not exist. I am very new to SSIS and trying find my way through please let me know if you need anymore information or if there is an easier way to combine all of these steps. Thanks

can you share the error. As for the code it is correct its just that i would suggest to use "casting" i.e. Dts.Variables["User::MyFiles"].Value = (string)Dts.Variables["User::MyFiles"].Value + "|" + (string)Dts.Variables["User::MyFile"].Value;Please mark the post as answered if it answers your question

DaniDe, Let's tackle issues one by one. Please tell us what is the exact error message and at which stage/component exactly. One common misconception about foreach loop is that it peeks at all the file names it needs to go through in advance and never does that again. So if you rename/move them in one loop-script, then you either need the new names captured into the variable immediately (yes, append the new names to the variable) or another loop to go through the new file names.Arthur My Blog

Sorry it takes forever to run this package. I had added 2 loops as suggested.I am trying now to change the variables in for the second loop that will move the files. I had recieved an error at move file that said the source was not a valid source. and at the same error at the rename file system task.

If the package does not run due to errors how come it takes forever to run it? The error hints to the package trying to operate on a file that is not present. Again, we would like to see more concrete evidence as package setup screenshots, the exact error message (omit any sensitive info).Arthur My Blog

here is the bottom half of the data flow it takes a long time because it is looping through a very large database before it gets to here. I would just like to insert a task that will allow for rename of the files that are going to be sent out. I might just go back through and try to figure out the C# code to rename the files, I often recieve the error: [File System Task] Error: An error occurred with the following error message: "Could not find file 'C:\Finance\PDR Monthly\Reports\Chase-7-2012.pdf'.". When I add the loop to rename the files, using the website link that was given.

I am not sure but I was hoping to configure it like the image below where it would move the .pdf files in the "Reports" folder to an "Archive" folder then loop through to get the data, script to a PDF, Loop through the new .pdf's in the "Reports" folder to rename the pdf's then script out to get all the files in the folder as attachments and send email them out. I am not sure if you have any suggestions, it was just what I was considering doing but it doesn't seem to work for me.

hi , u can try like this create a variable and in datepart use like this DATEPART(m, DATEADD(m, -1, getdate())) --this is for month DATEPART(y, DATEADD(m, -1, getdate()))----this is for year and follow the step and modify in date part u can get last month ..http://www.sqlnerd.com/ssis_dynamic_dates.htm---------------------------------------------------------------Please mark the post as answered if it answers your question

When I try to move the files out of the file folder with a foreach loop I recieve the following error: SSIS package "main (1).dtsx" starting. Warning: 0x0 at Log Start: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Warning: 0x0 at Log Start: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error: 0xC002F30D at MOVE FILE, File System Task: Variable "FullUserArchivePath" is used as a source or destination and is empty. Error: 0xC0024107 at MOVE FILE: There were errors during task validation. Warning: 0x80019002 at First foreachloop 1: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (2); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "main (1).dtsx" finished: Failure. I also tried it with the variables in the website link and with actual file names. I am just not sure.

I have tried that also but but I am not sure how to put implement that into c#. here is the code for the output path in c# and the current monthDayYear string monthDayYear = DateTime.Now.Months.ToSring() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Year.ToString(); public string outputPath { set { // _outPutPath = value + _customer + ".PDF"; _outPutPath = value + _customer + "-" + monthDayYear + ".PDF"; } } I have tried various codes but I think I am missingg something I have no expirience in c# so i am a quite lost I have tried the following coddes: string monthDayYear = DateTime.Dts.Variables["User::FilePath"].Value.ToString(); //User::FilePath= (DT_STR, 50,1252)DATEPART("mm",DATEADD("mm", -1, getdate())) + "-" + (DT_STR, 50,1252)DATEPART("yyyy",DATEADD("mm", -1, getdate())) ALSO string monthDayYear = DateTime.Now.Months(-1).ToSring() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Year.ToString(); AND DateTime LastMonthDate = DateTime.Now.AddMonths(-1); //Response.Write(LastMonthDate.ToString("MM/dd/yyy")); I am not sure what to write out in C# to link it back to the SSIS variable

hi u can give exp in for eachloop.. try this

Why on a non-working package you have a red mark on one of the components (seems it is Rename the file)? You need to fix it 1st. When you rename the file, what is its dynamic expression?Arthur My Blog

That was just an example of what I wanted to do and I didnt know if it was possible. The expressions are inconsistent and now I just need to move the files which is proving to be another difficult task. Thanks for your help.