Hi,
What is your question?
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>We have a customer who uses the dynamic port assignment
option when a connection is established, instead of the
default 1433. However, our application requires a fixed
port number, either the default 1433 or an explicitly set
port number.
>For now we are accessing this small database via the
ODBC connector using the Microsoft-supplied ODBC for
SQLServer driver. However, this is not a solution for
larger databases because of the additional overhead
involved.
>Any assistance will be very appreciated.
>Alex Ivascu
>(posted to server ng, by mistake)
>.
>This particular database server has multiple instances, and one of them is t
he one that we need to connect to. How do we know what port number to conne
ct to, with our application? Since the only things that we can specify in o
ur connector is a port numb
er and a server name...
Thanks.|||If the application is an ODBC application using MDAC 2.6 or greater, we'll
enumerate the ports and connect on the right one.
If you don't want that behavior, then set the ports to a fixed port and use
this in your connection string.
823938 How to use static and dynamic port allocation in SQL Server 2000
http://support.microsoft.com/?id=823938
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Showing posts with label port. Show all posts
Showing posts with label port. Show all posts
Monday, March 26, 2012
dynamic port connection
Hi,
What is your question?
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>We have a customer who uses the dynamic port assignment
option when a connection is established, instead of the
default 1433. However, our application requires a fixed
port number, either the default 1433 or an explicitly set
port number.
>For now we are accessing this small database via the
ODBC connector using the Microsoft-supplied ODBC for
SQLServer driver. However, this is not a solution for
larger databases because of the additional overhead
involved.
>Any assistance will be very appreciated.
>Alex Ivascu
>(posted to server ng, by mistake)
>.
>
This particular database server has multiple instances, and one of them is the one that we need to connect to. How do we know what port number to connect to, with our application? Since the only things that we can specify in our connector is a port numb
er and a server name...
Thanks.
|||If the application is an ODBC application using MDAC 2.6 or greater, we'll
enumerate the ports and connect on the right one.
If you don't want that behavior, then set the ports to a fixed port and use
this in your connection string.
823938 How to use static and dynamic port allocation in SQL Server 2000
http://support.microsoft.com/?id=823938
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
What is your question?
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>We have a customer who uses the dynamic port assignment
option when a connection is established, instead of the
default 1433. However, our application requires a fixed
port number, either the default 1433 or an explicitly set
port number.
>For now we are accessing this small database via the
ODBC connector using the Microsoft-supplied ODBC for
SQLServer driver. However, this is not a solution for
larger databases because of the additional overhead
involved.
>Any assistance will be very appreciated.
>Alex Ivascu
>(posted to server ng, by mistake)
>.
>
This particular database server has multiple instances, and one of them is the one that we need to connect to. How do we know what port number to connect to, with our application? Since the only things that we can specify in our connector is a port numb
er and a server name...
Thanks.
|||If the application is an ODBC application using MDAC 2.6 or greater, we'll
enumerate the ports and connect on the right one.
If you don't want that behavior, then set the ports to a fixed port and use
this in your connection string.
823938 How to use static and dynamic port allocation in SQL Server 2000
http://support.microsoft.com/?id=823938
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
dynamic port assignment
We have a customer who uses the dynamic port assignment option when a connec
tion is established, instead of the default 1433. However, our application
requires a fixed port number, either the default 1433 or an explicitly set p
ort number.
For now we are accessing this small database via the ODBC connector using th
e Microsoft-supplied ODBC for SQLServer driver. However, this is not a solu
tion for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex IvascuYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the po
rt numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>sql
tion is established, instead of the default 1433. However, our application
requires a fixed port number, either the default 1433 or an explicitly set p
ort number.
For now we are accessing this small database via the ODBC connector using th
e Microsoft-supplied ODBC for SQLServer driver. However, this is not a solu
tion for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex IvascuYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the po
rt numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>sql
dynamic port assignment
We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number.
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex Ivascu
You can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>
|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu
|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex Ivascu
You can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>
|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu
|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>
dynamic port assignment
We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved
Any assistance will be very appreciated
-
Alex IvascYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try
Thanks, Don.
Alex Ivasc|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved
Any assistance will be very appreciated
-
Alex IvascYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try
Thanks, Don.
Alex Ivasc|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>
Sunday, March 11, 2012
Dynamic FTP Connection
Hello ,
I have a table having different ftp url,user name, passwod, port no.I want to copy the file from all the location on my server at.
How can I change the connection string/ FTP location for FTP connection manager at rum time in SSIS.
Thanks
You should be able to set up a variable and set its value to the Connection expression in the FTP Task.
If you right click on the FTP task and then select edit. The left hand menu should show a link for expressions. Add a new expression for Connection and set it to the variable you have set up to store this value.
This variable can be changed at runtime using Package Configurations.
Does this help at all.
Grant|||
Hi,
Can you please explain this in brief. As I am new to SSIS thats why facing problem in doing that. Should I add all the varibles such as
Remote Path, ServerUserName,serverPassword,filename,port. and how at run time these would change.
Thanks
|||The way I do it is in a Script Task.
I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.
Here is my code:
PublicSub Main()
Dim ftpConn As ConnectionManager
ftpConn = Dts.Connections("ftp")
ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)
ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)
Dts.TaskResult = Dts.Results.Success
EndSub
|||Al C. wrote:
The way I do it is in a Script Task.
I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.
Here is my code:
PublicSub Main()
Dim ftpConn As ConnectionManager
ftpConn = Dts.Connections("ftp")
ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)
ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)
Dts.TaskResult = Dts.Results.Success
EndSub
I have tried this, but I have many values in the table.I think this would work only for the first or last row.And how can I load the values in the variable through execute sql task.I am getting error when I do so.
|||You can certainly do it this way.As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.
What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.
Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.
I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.
The below links also helped me greatly when looking at dynamic modification of SSIS packages:
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx
Cheers,
Grant|||
The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1
|||You can certainly do it this way. As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.
What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.
Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.
I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.
The below links also helped me greatly when looking at dynamic modification of SSIS packages:
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspxCheers,
Grant
Hi,
I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.
|||The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1
Hi,
I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.
|||You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.
There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.
There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
hi,
The above code worked for the iteration. Thanks a Lot for that. Now the problem is that How can a FTP connection Manager be configure dynamically in the script task. I have tried it but it fetches the file from only that location which I have given to configure it.
|||Have you set a breakpoint in the Script Task to verify that the FTP Connection Manager properties are being set to the correct values for each iteration of the loop? Once you have dynamically configurated the ftp connection manager in the Script task then the FTP Task itself should have its IsRemotePathVariable set to True and then set the RemoteVariable to the variable which is mapped during the loop.
|||Hi,
Thanks a lot Grant Swan ,Al C. I have the problem solved. This blog
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
helped a lot to complete the task.
|||Not sure if anyone will see this, but the disconnect I'm having on this problem is between the script task and the FTP task. The script task is getting data from my DB, but I don't know how to pass that into the FTP task. The package level configurations don't make sense, because the values need to be populated differently each time a ForEach Container scrolls through the recordset. It looks like the "expression" in the FTP task is the answer. But I don't know how to tell it to read the connection object from the script task. Maybe I need to pass it to a variable? Don't know how to do that either. Any help is greatly appreciated.Dynamic FTP Connection
Hello ,
I have a table having different ftp url,user name, passwod, port no.I want to copy the file from all the location on my server at.
How can I change the connection string/ FTP location for FTP connection manager at rum time in SSIS.
Thanks
You should be able to set up a variable and set its value to the Connection expression in the FTP Task.
If you right click on the FTP task and then select edit. The left hand menu should show a link for expressions. Add a new expression for Connection and set it to the variable you have set up to store this value.
This variable can be changed at runtime using Package Configurations.
Does this help at all.
Grant|||
Hi,
Can you please explain this in brief. As I am new to SSIS thats why facing problem in doing that. Should I add all the varibles such as
Remote Path, ServerUserName,serverPassword,filename,port. and how at run time these would change.
Thanks
|||The way I do it is in a Script Task.
I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.
Here is my code:
PublicSub Main()
Dim ftpConn As ConnectionManager
ftpConn = Dts.Connections("ftp")
ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)
ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)
Dts.TaskResult = Dts.Results.Success
EndSub
|||The way I do it is in a Script Task.
I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.
Here is my code:
PublicSub Main()
Dim ftpConn As ConnectionManager
ftpConn = Dts.Connections("ftp")
ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)
ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)
Dts.TaskResult = Dts.Results.Success
EndSub
I have tried this, but I have many values in the table.I think this would work only for the first or last row.And how can I load the values in the variable through execute sql task.I am getting error when I do so.
|||You can certainly do it this way.As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.
What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.
Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.
I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.
The below links also helped me greatly when looking at dynamic modification of SSIS packages:
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx
Cheers,
Grant|||
The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1
|||You can certainly do it this way. As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.
What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.
Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.
I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.
The below links also helped me greatly when looking at dynamic modification of SSIS packages:
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspxCheers,
Grant
Hi,
I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.
|||The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1
Hi,
I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.
|||You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.
There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.
There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
hi,
The above code worked for the iteration. Thanks a Lot for that. Now the problem is that How can a FTP connection Manager be configure dynamically in the script task. I have tried it but it fetches the file from only that location which I have given to configure it.
|||Have you set a breakpoint in the Script Task to verify that the FTP Connection Manager properties are being set to the correct values for each iteration of the loop? Once you have dynamically configurated the ftp connection manager in the Script task then the FTP Task itself should have its IsRemotePathVariable set to True and then set the RemoteVariable to the variable which is mapped during the loop.
|||Hi,
Thanks a lot Grant Swan ,Al C. I have the problem solved. This blog
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
helped a lot to complete the task.
|||Not sure if anyone will see this, but the disconnect I'm having on this problem is between the script task and the FTP task. The script task is getting data from my DB, but I don't know how to pass that into the FTP task. The package level configurations don't make sense, because the values need to be populated differently each time a ForEach Container scrolls through the recordset. It looks like the "expression" in the FTP task is the answer. But I don't know how to tell it to read the connection object from the script task. Maybe I need to pass it to a variable? Don't know how to do that either. Any help is greatly appreciated.Dynamic FTP Connection
Hello ,
I have a table having different ftp url,user name, passwod, port no.I want to copy the file from all the location on my server at.
How can I change the connection string/ FTP location for FTP connection manager at rum time in SSIS.
Thanks
You should be able to set up a variable and set its value to the Connection expression in the FTP Task.
If you right click on the FTP task and then select edit. The left hand menu should show a link for expressions. Add a new expression for Connection and set it to the variable you have set up to store this value.
This variable can be changed at runtime using Package Configurations.
Does this help at all.
Grant|||
Hi,
Can you please explain this in brief. As I am new to SSIS thats why facing problem in doing that. Should I add all the varibles such as
Remote Path, ServerUserName,serverPassword,filename,port. and how at run time these would change.
Thanks
|||The way I do it is in a Script Task.
I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.
Here is my code:
Public Sub Main()
Dim ftpConn As ConnectionManager
ftpConn = Dts.Connections("ftp")
ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)
ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
|||Al C. wrote:
The way I do it is in a Script Task.
I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.
Here is my code:
Public Sub Main()
Dim ftpConn As ConnectionManager
ftpConn = Dts.Connections("ftp")
ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)
ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
I have tried this, but I have many values in the table.I think this would work only for the first or last row.And how can I load the values in the variable through execute sql task.I am getting error when I do so.
|||You can certainly do it this way.As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.
What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.
Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.
I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.
The below links also helped me greatly when looking at dynamic modification of SSIS packages:
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx
Cheers,
Grant|||
The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1
|||You can certainly do it this way. As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.
What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.
Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.
I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.
The below links also helped me greatly when looking at dynamic modification of SSIS packages:
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspxCheers,
Grant
Hi,
I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.
|||The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1
Hi,
I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.
|||You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.
There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.
There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
hi,
The above code worked for the iteration. Thanks a Lot for that. Now the problem is that How can a FTP connection Manager be configure dynamically in the script task. I have tried it but it fetches the file from only that location which I have given to configure it.
|||Have you set a breakpoint in the Script Task to verify that the FTP Connection Manager properties are being set to the correct values for each iteration of the loop? Once you have dynamically configurated the ftp connection manager in the Script task then the FTP Task itself should have its IsRemotePathVariable set to True and then set the RemoteVariable to the variable which is mapped during the loop.
|||Hi,
Thanks a lot Grant Swan ,Al C. I have the problem solved. This blog
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
helped a lot to complete the task.
|||Not sure if anyone will see this, but the disconnect I'm having on this problem is between the script task and the FTP task. The script task is getting data from my DB, but I don't know how to pass that into the FTP task. The package level configurations don't make sense, because the values need to be populated differently each time a ForEach Container scrolls through the recordset. It looks like the "expression" in the FTP task is the answer. But I don't know how to tell it to read the connection object from the script task. Maybe I need to pass it to a variable? Don't know how to do that either. Any help is greatly appreciated.
Subscribe to:
Posts (Atom)