Dynamic object location

Hi, i would like to know if there's a way to set the objects location/size
based on user input parameters. I try to use expressions in location and size
properties but rs says that is a invalid value for the field.
Thanks
Jorge"Jorge Gonçalves" <JorgeGonalves@.discussions.microsoft.com> wrote in message
news:90CF28CC-066F-46BB-9664-75929DEE8EC3@.microsoft.com...
> Hi, i would like to know if there's a way to set the objects location/size
> based on user input parameters. I try to use expressions in location and
size
> properties but rs says that is a invalid value for the field.
> Thanks
> Jorge|||I am interested in doing this as well. In particular I want to be able to
compute the height of a rectangle control based on data values.
Bill
"Jorge Gonçalves" <JorgeGonalves@.discussions.microsoft.com> wrote in message
news:90CF28CC-066F-46BB-9664-75929DEE8EC3@.microsoft.com...
> Hi, i would like to know if there's a way to set the objects location/size
> based on user input parameters. I try to use expressions in location and
size
> properties but rs says that is a invalid value for the field.
> Thanks
> Jorge

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

Hi,

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

|||

Grant Swan wrote:

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

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.

|||

Al C. wrote:

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

|||

Al C. wrote:

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

Hi,

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

|||

Grant Swan wrote:

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

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.

|||

Al C. wrote:

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

|||

Al C. wrote:

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

Hi,

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

|||

Grant Swan wrote:

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

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.

|||

Al C. wrote:

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

|||

Al C. wrote:

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 File location for DTS transfer

Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directoryand file structure will be a folder with a client name (can be calledanything) and it will have files in it (these files will have the samefilenames as all the other directories. So I will have folderJimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue withfiles a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location toa DTS package so I can import all the data from the text file into aSQL server. The way the SQL server will be set up is that eachFolder from the FTP site will be a separate Database and each file will1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the filelocation to pull all those files and then importing them to the properdatabase.
I'm not limiting the solution to DTS packages so if .NET can beincorporated to make it easier then so be it. But keep in mind Ican have up to 200 folders with 12 - 20 text files ranging fromhundreds of rows of data to many thousands of rows. And thepackage needs to be ran twice a day so time/performance is anissue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
How are you executing the package? If you execute dynamically, such as through the dtsrun utility or through SQLDMO code, you should be able to pass a value into a global variable. Then use the dynamic properties task to change the default global variable value to your new value.|||I've used DTS Run before so that's the only way I know how to dothat. How would I use the dynamic properties task to change thedefault global variable value? Can you give me a snippet,pseuodcode or something of how that would work?
Never heard of SQLDMO, what is that?
|||

netflash99 wrote:

Never heard of SQLDMO, what is that?

SQLDMO(SQL Server Data Management Object) Microsoft property it creates everything you do with Enterprise Manager manually through code but it uses System tables from the master to do its work so your code will be orphaned in SQL Server 2005 where those tables are really Microsoft Property you because cannot use them. DTS Run is good practice. Try the url below on using Global Variable. Hope this helps.

http://www.sqldts.com

Dynamic column in the query using SQL 2005

Hi All,

I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.

I have a table transac table fields are likely,

location,date,amount values,

USA,01/07/2006,3000

SG,01/07/2006,2500

USA,02/07/2006,6000

SG,02/07/2006,3500

USA,03/07/2006,1000

SG,03/07/2006,6700

USA,04/07/2006,500

SG,04/07/2006,200

Am writing query for date = 04/07/2006

select location,date,amount from transac where date = 04/07/2006

I wanted to add two more column in the query which is

a.two days before what is the amount

b. From 01/07/2006 to 04/07/2006 what is the amount

The result I want to be

Location,date,amount,2daysbefore,uptodate

USA,04/07/2006,500,6000,10500

SG,04/07/2006,200,3500,12900

How to write a query ?.

I am writing this query from DataSet for Report Desinger.

Is there any way to include this two column.

Please Advise,

Regrads Saleem

Here is the query in bold, the rest if for creating a tmp table with approx values like the ones you use. NB date format is MM/DD/YYYY.

create table #x
(
country varchar(10),
Date datetime,
PRICE1 decimal(9,2),
)
insert #x
select 'USA', '1/1/2006', 3000 union all
select 'SG', '1/1/2006', 2500 union all
select 'USA', '1/2/2006', 2500 union all
select 'SG', '1/2/2006', 1500 union all
select 'USA', '1/3/2006', 1000 union all
select 'SG', '1/3/2006', 7550 union all
select 'USA', '1/4/2006', 500 union all
select 'SG', '1/4/2006', 300 union all
select 'USA', '1/5/2006', 350 union all
select 'SG', '1/5/2006', 400

select
country,
date,
price1 as dayAmount,
(select price1 from #x as b where datediff(dd,b.date,a.date) = 2 and a.country = b.country) as prevDayAmount,
(select sum(price1) from #x as b where datediff(mm,b.date,a.date) < 1 and a.country = b.country) as sumMonthAmount
from #x as a
where date = '01/03/2006'

drop table #x

Dynamic change of the column size and location of a matrix

I have a report that has a matrix. That matrix can have from 2 to 16 columns dependinging on the dataset result. Right now I am forced to place this matrix on the left side of the report and make a column layout pretty narrow. When dataset has more than 13 or so columns it looks OK, but when dataset has only two or three columns it looks weird with a matrix sitting in the left corner with two or three narrow columns and a lot of empty space to the right.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

Thank you,

Simon.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

No

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

No

Curently, the RS object model doesn't support manupaluting the report definition at runtime. Please take a moment to put these on the wish list for a future release on connect.microsoft.com

|||Is the feature available yet or is it still a no go. I really need to be able to change the width of columns and the height of textboxes dynamically. Has anyone been able to acomplish this?
|||

No go, as far as I know.

:-(

|||

The only way to do this that I know of is to re-write the RDL dynamically at run time with the specifications that you need for this run. Does this help at all? There are probably only a couple of changes that you would need to make in your case.

>L<

|||Yeah that's what I have done. When the user selects the report from my web app, I load the rdl file, make the necessary changes to the xml, re-deploy the report and then generate it. A bit of a pain but unfortunately the only work around at the moment.
|||

Hi Mark,

One can imagine that something similar would be necessary if dynamic width were built into the product.

Unfortunately it would pretty much take the same work *and* -- this is the kicker that may have taken it off the Katmai table for all we know -- it might be very difficult to get it right for every type of scenario that people might want to use it in.

I think it would be a great idea for Katmai or some future version to provide a hook that allowed a "swap event" during the report rendering process (there's a reason I call it that, although I know it sounds odd <s>).

The basic idea here is that the kinds of work we need to do at this point in the process would still be up to us, the product wouldn't do the work of adjusting the RDL, but it would offer a safe and consistent point at which to do that work. It would maybe hand the RDL as a stream or a loaded DOM object, we could make whatever adjustments we wanted, and then it would continue on its way.

Would you agree that this would provide some benefit, or do you see it as not sufficiently "automagical"?

>L<

|||I think that any improvement in this area would be beneficial however I would prefer for the process to be a bit more "automagical" as you put it.

Dynamic change of the column size and location of a matrix

I have a report that has a matrix. That matrix can have from 2 to 16 columns dependinging on the dataset result. Right now I am forced to place this matrix on the left side of the report and make a column layout pretty narrow. When dataset has more than 13 or so columns it looks OK, but when dataset has only two or three columns it looks weird with a matrix sitting in the left corner with two or three narrow columns and a lot of empty space to the right.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

Thank you,

Simon.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

No

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

No

Curently, the RS object model doesn't support manupaluting the report definition at runtime. Please take a moment to put these on the wish list for a future release on connect.microsoft.com

|||Is the feature available yet or is it still a no go. I really need to be able to change the width of columns and the height of textboxes dynamically. Has anyone been able to acomplish this?
|||

No go, as far as I know.

:-(

|||

The only way to do this that I know of is to re-write the RDL dynamically at run time with the specifications that you need for this run. Does this help at all? There are probably only a couple of changes that you would need to make in your case.

>L<

|||Yeah that's what I have done. When the user selects the report from my web app, I load the rdl file, make the necessary changes to the xml, re-deploy the report and then generate it. A bit of a pain but unfortunately the only work around at the moment.
|||

Hi Mark,

One can imagine that something similar would be necessary if dynamic width were built into the product.

Unfortunately it would pretty much take the same work *and* -- this is the kicker that may have taken it off the Katmai table for all we know -- it might be very difficult to get it right for every type of scenario that people might want to use it in.

I think it would be a great idea for Katmai or some future version to provide a hook that allowed a "swap event" during the report rendering process (there's a reason I call it that, although I know it sounds odd <s>).

The basic idea here is that the kinds of work we need to do at this point in the process would still be up to us, the product wouldn't do the work of adjusting the RDL, but it would offer a safe and consistent point at which to do that work. It would maybe hand the RDL as a stream or a loaded DOM object, we could make whatever adjustments we wanted, and then it would continue on its way.

Would you agree that this would provide some benefit, or do you see it as not sufficiently "automagical"?

>L<

|||I think that any improvement in this area would be beneficial however I would prefer for the process to be a bit more "automagical" as you put it.

Dynamic change of the column size and location of a matrix

I have a report that has a matrix. That matrix can have from 2 to 16 columns dependinging on the dataset result. Right now I am forced to place this matrix on the left side of the report and make a column layout pretty narrow. When dataset has more than 13 or so columns it looks OK, but when dataset has only two or three columns it looks weird with a matrix sitting in the left corner with two or three narrow columns and a lot of empty space to the right.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

Thank you,

Simon.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

No

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

No

Curently, the RS object model doesn't support manupaluting the report definition at runtime. Please take a moment to put these on the wish list for a future release on connect.microsoft.com

|||Is the feature available yet or is it still a no go. I really need to be able to change the width of columns and the height of textboxes dynamically. Has anyone been able to acomplish this?
|||

No go, as far as I know.

:-(

|||

The only way to do this that I know of is to re-write the RDL dynamically at run time with the specifications that you need for this run. Does this help at all? There are probably only a couple of changes that you would need to make in your case.

>L<

|||Yeah that's what I have done. When the user selects the report from my web app, I load the rdl file, make the necessary changes to the xml, re-deploy the report and then generate it. A bit of a pain but unfortunately the only work around at the moment.
|||

Hi Mark,

One can imagine that something similar would be necessary if dynamic width were built into the product.

Unfortunately it would pretty much take the same work *and* -- this is the kicker that may have taken it off the Katmai table for all we know -- it might be very difficult to get it right for every type of scenario that people might want to use it in.

I think it would be a great idea for Katmai or some future version to provide a hook that allowed a "swap event" during the report rendering process (there's a reason I call it that, although I know it sounds odd <s>).

The basic idea here is that the kinds of work we need to do at this point in the process would still be up to us, the product wouldn't do the work of adjusting the RDL, but it would offer a safe and consistent point at which to do that work. It would maybe hand the RDL as a stream or a loaded DOM object, we could make whatever adjustments we wanted, and then it would continue on its way.

Would you agree that this would provide some benefit, or do you see it as not sufficiently "automagical"?

>L<

|||I think that any improvement in this area would be beneficial however I would prefer for the process to be a bit more "automagical" as you put it.

Dynamic change of the column size and location of a matrix

I have a report that has a matrix. That matrix can have from 2 to 16 columns dependinging on the dataset result. Right now I am forced to place this matrix on the left side of the report and make a column layout pretty narrow. When dataset has more than 13 or so columns it looks OK, but when dataset has only two or three columns it looks weird with a matrix sitting in the left corner with two or three narrow columns and a lot of empty space to the right.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

Thank you,

Simon.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

No

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

No

Curently, the RS object model doesn't support manupaluting the report definition at runtime. Please take a moment to put these on the wish list for a future release on connect.microsoft.com

|||Is the feature available yet or is it still a no go. I really need to be able to change the width of columns and the height of textboxes dynamically. Has anyone been able to acomplish this?
|||

No go, as far as I know.

:-(

|||

The only way to do this that I know of is to re-write the RDL dynamically at run time with the specifications that you need for this run. Does this help at all? There are probably only a couple of changes that you would need to make in your case.

>L<

|||Yeah that's what I have done. When the user selects the report from my web app, I load the rdl file, make the necessary changes to the xml, re-deploy the report and then generate it. A bit of a pain but unfortunately the only work around at the moment.
|||

Hi Mark,

One can imagine that something similar would be necessary if dynamic width were built into the product.

Unfortunately it would pretty much take the same work *and* -- this is the kicker that may have taken it off the Katmai table for all we know -- it might be very difficult to get it right for every type of scenario that people might want to use it in.

I think it would be a great idea for Katmai or some future version to provide a hook that allowed a "swap event" during the report rendering process (there's a reason I call it that, although I know it sounds odd <s>).

The basic idea here is that the kinds of work we need to do at this point in the process would still be up to us, the product wouldn't do the work of adjusting the RDL, but it would offer a safe and consistent point at which to do that work. It would maybe hand the RDL as a stream or a loaded DOM object, we could make whatever adjustments we wanted, and then it would continue on its way.

Would you agree that this would provide some benefit, or do you see it as not sufficiently "automagical"?

>L<

|||I think that any improvement in this area would be beneficial however I would prefer for the process to be a bit more "automagical" as you put it.|||

I had a similar requirement and after I read this thread it dawned on me!!! Why not copy the matrix or whatever object and duplicate it. One for each size and hide all but one. Then at run time show the one you want at the right size and position.

I know, not the best idea, but it's something.

|||

It's difficult to do that with the matrix because you don't have as much control over what is shown where... it works in some cases, but I end up using tables when I want to do stuff like this (where you can be less random about what is going to be in each column, you can write more targeted visibility instructions for each).

Unless you mean just have two matrices ? This only works if each column should get the same width within each case, not if they are mixed width.

>L<