Thanks for your reply ArthurZ,

I have 100s of files. We use Leech process to FTP the file and unzip it. but now its need to replace the leech process and have SSIS does the decent job.

there are different criteria for different files. (different location, username, password)
To automate the process, so i dont need to create 100 different connections for 100 files, I'm following this method. For test purpose, I am using only 2 files. I've thaat information in table.
I also figured out, how to unzip it in SSIS with the help of erlier posts.
but now, I need to find out how to create each connection, FTP the file in every connection, and save that locally.

Please let me know if you require any more information.

Thanks,

There is an amazing pack of free network admin tools. click here to download it

I offer you to go with an Execute SQL Task like here

http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

then make a ForEach loop to go through the rows obtained from the config table (that are in the object type of a variable) set to Multiple Rows in the Execute SQL Task's Result set portion.
The row from the config table gets parsed and package variables DataFeedLocation, UserName, Password, FolderName, FileName get populated using a Script Task.
And finally, inside this ForEach loop you use the FTP task with an expression consuming the aforementioned variables to get the needed files.Arthur My Blog

Need to support users over the internet? click here try our remote control online beta

As you said to assign table data to variables, I've used Data Flow Task as OLE DB as Source and Record Set as Destination.
After assiging the columns to the variables from data set, the next step is FTP Task.

I have different file locations on different feed locations.
Like

aftp.abcd.com
aftp.xyz.com

Is that doable to get different FTP Server name as variable?
Also next question after this step will be -
After connecting to the FTP Server, in FTP Task Editor, we can see all the folder structure of the FTP Server.
In my requirement, I may have some files from the same location with different folder name and different file name.
My guess is to assign file name with folder
(Like /1123/ancd.txt.gz from 1123 folder name and ancd.txt.gz file name)

Again, thanks for all your help

Need to support users over the internet? click here try our remote control online beta

I apologize for asking small questions.
1) Execute SQL Task should be outside the ForEach loop. Is that correct?
2) Multiple row (Full Result Set) from Result Set option in Execute SQL Task should be selected.
3) Assign the values of column through Script Task
(This step 3 should be inside the ForEach loop)
Issue/Note: (Can step 3 can be done through Data Flow Task and assign result set to the record set?)

Need to support users over the internet? click here try our remote control online beta

Re #1 - yes, it must be executed once to just get the recordset from the config table.
Yes to #2 and #3.
There is no data flow in step #3.Arthur My Blog

Need to support users over the internet? click here try our remote control online beta

Hi all,
Following the Arthur methodology:
1. Put a ExecuteSQL outside any container to retrieve the config table stored SQL, the result set must be assign to a object variable (that will be a recordset)
2. Put a For Each cotainer to loop over the previous point variable, you can loop the recordset using Foreach ADO Enumerator. On each loop take a row that you should assign to your varaibles: DataFeedLocation, UserName, Password, FolderName, FileName.
3. Add the FTP Task inside Foreach and configure the properties with expression taking your variables values.
And run!
Regards.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it.
http://bifase.blogspot.com
|
http://twitter.com/atharky


Need to support users over the internet? click here try our remote control online beta

I am referring to the question I had asked

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ee6dc412-342f-4afe-a7de-44d7ca75417c


I apologize that I had to go away from this project for couple of weeks. Now I'm back to this project. I need some of your help.

I have created File System Task which deletes the existing files, FTP Task which gets file from FTP and Execute Process task which unzips the zip files.
Thanks to Reza Raad, Shahrihar Nikkah.
Now I also created a table with FTP Details which includes, DataFeedLocation, UserName, Password, FolderName, FileName.

I'm stuck at this point. for many of the files, the DataFeedLocation is same. I created this table to get information as said in the previous post.

What should be the next steps?

Thanks a lot in advance

There is an amazing pack of free network admin tools. click here to download it

This looks to me a config table. Did you stuck because you do not know hot get the config values from this table and use them in your package?Arthur My Blog

Need to support users over the internet? click here try our remote control online beta

I'm still stuck in Creating Dynamic FTP Connection Manager from the table dataset.
Please provide some suggestions.

There is an amazing pack of free network admin tools. click here to download it

I was told to try SSIS Configuration. Is that doable with SSIS Package Configuration?

Need to support users over the internet? click here try our remote control online beta

You should be looking into expressions, not configuraitons. Expressions are evaluated at the time that the property is accessed (i.e. changes during execution of package) while (using a simplified explanation of) configurations are loaded once as the
package is loaded.
You would assign the value of the items such as user name, file location, etc to a set of variables in the for each loop that is parsing the records gathered from the execute sql task. You would, in your ftp task, use an expression on the various properties
you need to change dynamically to set the value for these items based off of the variables which you just populated from the loop.
To be honest, I don't remember the degree to which the stock FTP task allows you to set these items via expression. If it does not, you may have to roll your own FTP via a script task. Please mark answered posts. Thanks for your time.

There is an amazing pack of free network admin tools. click here to download it

It looks like you will want to build the connection string for the ftp connection manager through an expression. Servername (or IP).Port.Username.Password

See the following thread:
FTP Connection Manager: Set FTP Password using a variablePlease mark answered posts. Thanks for your time.

There is an amazing pack of free network admin tools. click here to download it

It looks like you will want to build the connection string for the ftp connection manager through an expression. Servername (or IP).Port.Username.Password

See the following thread:
FTP Connection Manager: Set FTP Password using a variablePlease mark answered posts. Thanks for your time.

There is an amazing pack of free network admin tools. click here to download it

Thank you Eric for your reply.

I tried the Expression to change the connection manager properties. but having some issues with it. can't figure out how to change the password for FTP?
Do I need to use Package Configuration for that? I need some help plz

Need to support users over the internet? click here try our remote control online beta

Thank you Eric for your reply.

I tried the Expression to change the connection manager properties. but having some issues with it. can't figure out how to change the password for FTP?
Do I need to use Package Configuration for that? I need some help plz

There is an amazing pack of free network admin tools. click here to download it

Using Script

Public
Sub
Main()
Dim
ftpConnectionManager As
ConnectionManager

Dim varFileName As String
'Dim varFolderName As String

varFileName = "/" + Dts.Variables("User::FolderName").Value
Dts.Variables("User::FileName").Value = varFileName
ftpConnectionManager = Dts.Connections(
"FTP Connection Manager"
)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerName"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpServer" ).Value)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerPort"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpPort" ).Value)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerUserName"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpUser" ).Value)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerPassword"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpPassword" ).Value)
Dts.TaskResult = Dts.Results.Success
End
Sub



Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.

Need to support users over the internet? click here try our remote control online beta

Using Script

Public
Sub
Main()
Dim
ftpConnectionManager As
ConnectionManager

Dim varFileName As String
'Dim varFolderName As String

varFileName = "/" + Dts.Variables("User::FolderName").Value
Dts.Variables("User::FileName").Value = varFileName
ftpConnectionManager = Dts.Connections(
"FTP Connection Manager"
)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerName"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpServer" ).Value)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerPort"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpPort" ).Value)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerUserName"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpUser" ).Value)
Dts.Connections(
"FTP Connection Manager" ).Properties(
"ServerPassword"
).SetValue(ftpConnectionManager, Dts.Variables(
"FtpPassword" ).Value)
Dts.TaskResult = Dts.Results.Success
End
Sub



Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.

There is an amazing pack of free network admin tools. click here to download it