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