Hi,i have to upload file in ftp...i have a folder say (Report) containing file eg. 20090816 (where 16 is date) now i want to upload file daily of previous date..i managed to have a file of Previous Sate Report e.g (on 19 Morning i have file named 20090818 i nReport folder)..Now please tell me how would i upload file in ftp using above Thanks Amit

Hi, You need to use the FTP Task. http://msdn.microsoft.com/hi-in/library/ms137656%28en-us,SQL.90%29.aspx What you could do is use the file expression u used to create the source file in the FTP. For the source file u will use a flat file connection. In this connection u can again set up the connection string to the same expression.Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi Sudeep,I have found the Solution Thanks a lot for your Quick and Knowlegable ResponceAmit

Hi sudeep,What Problem i am facing is that when i run the Package File get Uploaded into the FTP Site,But when i want to run it with the help of Job Following Error Will Occur...........Executed as user: INDIAPREPAID\oxigendb. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:35:05 PM Error: 2009-08-18 14:35:05.54 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2009-08-18 14:35:05.79 Code: 0xC001602A Source: TataSkyFTP Connection manager "FTPSite" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2009-08-18 14:35:05.79 Code: 0xC002918F Source: FTP Task FTP Task Description: Unable to connect to FTP server using "FTPSite". End Error DTExec: The package execution returned DTSER_FAILURE (1... The package execution fa... The step failed.Please Suggest What mistake i have commited.Amit

Hi, Do you have a config file. Have you set the protectionLevel to EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword? If yes set to to DontSaveSensitive. Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi Sudeep,No i dont have config File .Please tell me where to change Protection LevelAmit

What is the protection level set. Check it on the control flow click any where on the white space & F4.There u can see the above property.Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi,please check Setting the Protection Level of Packagesthanks-Let us TRY this | Dont forget to mark the post(s) that answered your question

Hi ETL vs ELTL, when i changed protectionLevel to DontSaveSensitive. following error comes when i run the package ,in ftp i have given Passward SSIS package "TataSkyFTP.dtsx" starting. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Error: 0xC001602A at TataSkyFTP, Connection manager "FTPSite": An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . Error: 0xC002918F at FTP Task, FTP Task: Unable to connect to FTP server using "FTPSite". Task failed: FTP Task Warning: 0x80019002 at TataSkyFTP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) 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. SSIS package "TataSkyFTP.dtsx" finished: Failure.Amit

Hi sudeep, when i changed protectionLevel to DontSaveSensitive. following error comes when i run the package ,in ftp i have given Passward SSIS package "TataSkyFTP.dtsx" starting. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Error: 0xC001602A at TataSkyFTP, Connection manager "FTPSite": An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . Error: 0xC002918F at FTP Task, FTP Task: Unable to connect to FTP server using "FTPSite". Task failed: FTP Task Warning: 0x80019002 at TataSkyFTP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) 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. SSIS package "TataSkyFTP.dtsx" finished: Failure.Amit

If you use Dont SaveSensitive option no passwords will be saved in ur package and u will have to use a config file to store the passwords.In case u select any other protection level like encryptSensitiveWithUserKeyCheck this link:http://support.microsoft.com/kb/918760I would advise you to use the 4th Method thats DontSaveSensitive with config fileHow to set up config file:http://msdn.microsoft.com/en-us/library/cc895212.aspxHope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi sudeep,Please suggest me how to do with encryptSensitiveWithUserKey tel me way to run the package with the help of jo bAmit

Method 1: Use a SQL Server Agent proxy account Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions. This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account. Thats what Microsoft says.Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi sudeep,i am got puzzled which to use...........Finally i realised you are correct i should go for DontSaveSensitive Option..Initially i have created one package in which i have used DataFlow Task and FTPTask i Control Flow--------okIn Ftp i have given username and password..For source for ftp i have created File connection management editor and provide the Path wit the help of expression and then i run the package and i got my file uploaded in FTp Directory where i used to See file with the help of USername and password which i have given ib the FTP.But when i want to do this with the help of Job following error will occur Executed as user: INDIAPREPAID\oxigendb. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:35:05 PM Error: 2009-08-18 14:35:05.54 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2009-08-18 14:35:05.79Code: 0xC001602A Source: TataSkyFTP Connection manager "FTPSite" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2009-08-18 14:35:05.79 Code: 0xC002918F Source: FTP Task FTP Task Description: Unable to connect to FTP server using "FTPSite". End Error DTExec: The package execution returned DTSER_FAILURE (1... The package execution fa... The step failed.For this you have told me to change Protection levele to DontSaveSensitive option in Protection level den i run but again error come bt this time in running package ,for this you have told me to create config file,,but i am not able to create config file Please suggest me Amit

Hi Amit,Regarding the config file I will reply in ur new thread.http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/77e6e012-1cbc-4c27-904e-27a6a1dae644Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

When i check protection level in CSVSOURCE in configfile creationand take dontsaveoption in Protection in property following error will occur Information: 0x40016041 at TataSkyFTP: The package is attempting to configure from the XML file "E:\TataSky\TataSky\TataSky\TataSky.dtsConfig". SSIS package "TataSkyFTP.dtsx" starting. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Error: 0xC001602A at TataSkyFTP, Connection manager "FTPSite": An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . Error: 0xC002918F at FTP Task, FTP Task: Unable to connect to FTP server using "FTPSite". Task failed: FTP Task Warning: 0x80019002 at TataSkyFTP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) 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. SSIS package "TataSkyFTP.dtsx" finished: Failure. Information: 0x40016041 at TataSkyFTP: The package is attempting to configure from the XML file "E:\TataSky\TataSky\TataSky\TataSky.dtsConfig". SSIS package "TataSkyFTP.dtsx" starting. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Error: 0xC001602A at TataSkyFTP, Connection manager "FTPSite": An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . Error: 0xC002918F at FTP Task, FTP Task: Unable to connect to FTP server using "FTPSite". Task failed: FTP Task Warning: 0x80019002 at TataSkyFTP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) 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. SSIS package "TataSkyFTP.dtsx" finished: Failure.AMit

Please suggest when i opt DontSaveSensitive Option ,what should i do in config file AMit'

If you open ur config file.Search for "FTPSite". Check in the conncetion string do you have the password property?It would not be there.Edit the file and add the password manually. Password=FTPPassword;Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi ,this is my config file , please suggest where to Write <?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="INDIAPREPAID\Administrator" GeneratedFromPackageName="TataSkyFTP" GeneratedFromPackageID="{27352A70-DA84-45AA-BC0F-BD3C0D403C6D}" GeneratedDate="8/18/2009 5:59:58 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[CSVSOURCE].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>E:\TataSky\Reports\OxigenRecon20090817.CSV</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[FTPSite].Properties[ServerPassword]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[FTPSite].Properties[ServerUserName]" ValueType="String"><ConfiguredValue>tataftpuser</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Variables[User::DirectoryPath].Properties[Value]" ValueType="String"><ConfiguredValue>E:\TataSky\Reports</ConfiguredValue></Configuration></DTSConfiguration>Amit

<Configuration ConfiguredType="Property" Path="\Package.Connections[FTPSite].Properties[ServerPassword]" ValueType="String"><ConfiguredValue>FTPPASSWORD</ConfiguredValue>Add the password where I have entered the value in bold.Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi Sudeep, Please tell what should i select in select Property to Export in Package Configuration Wizard Regarsd Amit

Hi Amit,Are you interested in saving any thing else apaart from FTP connection string as of now?If no then, Minimise all the nodes on the object pannel.expand ur package name.You will see a node as "Connection Managers" expand it.In connection managers u will find all the connections that you have made in ur package.Expand the FTP Connection.Expand the properties. And check the connection sting thats enough.click the next button and give a name as asked.Now check the XML config file created in ur location.Modify the connection string to :"FTPSERVER:21.UserNAME.PASSWORD"Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Now i got my ftp file uploaded by doing thisConfiguration ConfiguredType="Property" Path="\Package.Connections[FTPSite].Properties[ServerPassword]" ValueType="String"><ConfiguredValue>FTPPASSWORD</ConfiguredValue>In place of FTPPASSWORD i have given my password but the problem is -----------when i open my FTP Directory ,when i enter URL of FTP Directory Server ,it will open the directory without asking for the Password,but i want when i enter password it will ask for username and password dor securityreason..Please suggestAMit

Hi Amit,Where are u entering the URL??How are u opening the FTP Directory? Please provide adequate details to solve the query fast.Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi Sudeep,Fianlly i got where i have mistaken .............Thanks for your Reponses ,without those it would be very diffidult for me come across.RegardsAmit Srivastava

Nice to hear that.Please close all the threads that you have craeted relating to this issue & last issues.Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Hi Sudeep,Fianlly i got where i have mistaken .............Thanks for your Reponses ,without those it would be very diffidult for me come across.RegardsAmit Srivastava What was the mistake u were doing?Put it in the thread may be it could help some one.Hope this helps !! Please mark the post(s) as Answered / Helpful that helped you - Sudeep

Sudeep, The goal is to post a flat file where the file name will change based on date. I have figured out how to get the flat file to save in the correct naming convention. I'm stumbling on how to get the FTP task to pull the correct file. FlatFileManager ConnectionString = "S:\\Greggs_Export_Folder\\TEST_EXPORT_FTP_"+(DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + ".TXT" I tried placing the same expression in the FTP Connection manager and received the following error when I run. Can you assist? ERROR MESSAGE TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at FTP_Export_Test [Connection manager "FTP Connection Manager"]: The port specified in the connection string is not valid. The ConnectionString format is ServerName:Port. Port should be an integer value between 1 and 65535. Error at FTP_Export_Test: The result of the expression ""S:\\Greggs_Export_Folder\\TEST_EXPORT_FTP_"+(DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + ".TXT"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. Error at FTP Task [FTP Task]: Type of Connection "TEST_EXPORT_FTP_20101206.TXT" is Not a file. Error at FTP Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)