Dynamic selection of flat file

I am writing a package where the user uploads a flat file to a web folder. I need to automate this package to run everytime it sees a new file.

How can I implement this?

Can I make a call to a package or a sql server job to run from .net 2.0?

Do I need to use a service broker to look for a new file and run the package or a stored proc....I am looking for an async process where user doesnt have to wait for the package to run as it involves data validation of flat file and its huge...

Please help!!

How about a scheduled package that runs every minute, and if it finds a file it runs the load task, otherwise it just ends.

You could do something event driven with the WMI event task, but I dislike that since it does not tell you what file has been found, or try the File Watcher Task (http://www.sqlis.com/default.aspx?23)

|||

Appreciate your response...

I was wondering about creating a config file in the database and update the Connection string value everytime a new file is uploaded with the new file name

And then call the package to run by adding dts assembly to visual studio and doing package.load....

Any suggestion or comments on this as I really dont want to run this every min....

Dynamic search with xml?

We have a table like this:
CREATE TABLE [Account](
[ID] [int] NULL,
[Name] [nvarchar](20) NULL,
[Status] [nvarchar](20) NULL
)
We have an xml file that defines search parameters that looks like
this:
<SearchParms>
<Parm col="ColToSearchOn" val="ValToSearchFor"/>
</SearchParms>
The basic idea is to create a stored procedure that accepts an xml doc
as parameter and then searchs the account table based on the parameters
passed in the xml file.
For example, if this is the doc passed into the sproc:
<SearchParms>
<Parm col="ID" val="123"/>
</SearchParms>
Then the sproc will eval something like:
SELECT *
FROM ACCOUNT
WHERE ID = 123
If the doc looks like this:
<SearchParms>
<Parm col="ID" val="123"/>
<Parm col="Name" val="Straus, LLC"/>
</SearchParms>
Then the sproc will eval something like:
SELECT *
FROM ACCOUNT
WHERE ID = 123
AND Name = 'Straus, LLC'
So, I'm wondering if there is a way to create a join between the xml
file and the relational table that will return the results I'm wanting.
I was hoping to be able to utilize some fancy XQuery type of stuff
rather than dynamically build a string based on the xml and then run
the sp_executesql method on the string.
I'm open to suggestions on xml format. I've only included the above as
an example. Ideas? Thanks for any help.Something like,
select * from account
where
id in
(
select a.b.value('(.)[1]', 'int')
from @.xml.nodes('/descendant::@.val')
)
Pohwan Han. Seoul. Have a nice day.
"Chris Kilmer" <christopherkilmer@.gmail.com> wrote in message
news:1138404194.372843.253320@.f14g2000cwb.googlegroups.com...
> We have a table like this:
> CREATE TABLE [Account](
> [ID] [int] NULL,
> [Name] [nvarchar](20) NULL,
> [Status] [nvarchar](20) NULL
> )
>
> We have an xml file that defines search parameters that looks like
> this:
> <SearchParms>
> <Parm col="ColToSearchOn" val="ValToSearchFor"/>
> </SearchParms>
> The basic idea is to create a stored procedure that accepts an xml doc
> as parameter and then searchs the account table based on the parameters
> passed in the xml file.
> For example, if this is the doc passed into the sproc:
> <SearchParms>
> <Parm col="ID" val="123"/>
> </SearchParms>
> Then the sproc will eval something like:
> SELECT *
> FROM ACCOUNT
> WHERE ID = 123
> If the doc looks like this:
> <SearchParms>
> <Parm col="ID" val="123"/>
> <Parm col="Name" val="Straus, LLC"/>
> </SearchParms>
> Then the sproc will eval something like:
> SELECT *
> FROM ACCOUNT
> WHERE ID = 123
> AND Name = 'Straus, LLC'
> So, I'm wondering if there is a way to create a join between the xml
> file and the relational table that will return the results I'm wanting.
> I was hoping to be able to utilize some fancy XQuery type of stuff
> rather than dynamically build a string based on the xml and then run
> the sp_executesql method on the string.
> I'm open to suggestions on xml format. I've only included the above as
> an example. Ideas? Thanks for any help.
>|||If your column names would be fixed, Han's suggestion may work.
However, if you need to create different predicates and you query against a
relational table, you either have to make the table into an XML datatype
itself (e.g using FOR XML) and then use local-name() to compare against the
col attribute or use dynamic SQL.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OJBUABxJGHA.1320@.TK2MSFTNGP15.phx.gbl...
> Something like,
> select * from account
> where
> id in
> (
> select a.b.value('(.)[1]', 'int')
> from @.xml.nodes('/descendant::@.val')
> )
> --
> Pohwan Han. Seoul. Have a nice day.
> "Chris Kilmer" <christopherkilmer@.gmail.com> wrote in message
> news:1138404194.372843.253320@.f14g2000cwb.googlegroups.com...
>sql

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

sql

Dynamic Properties task Config file

I'm using a INI file with my Dynamic Properties task for the data sources for my connections in the package. Say the package goes from Q/A to production and the servers change. Now assuming the ini file is changed with the correct (new) server names etc. Where do we specify the location of the INI file?

This is really frustrating. Please helpHowdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG|||Originally posted by sqlguy7777
Howdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG

Yeah i figured that but once i move it to a different server i dont want to go through all the packages and changes the config file location. so i was wondering if it could be done through another way where promoting the package to another server w/o actually designing the packages all over again.

Thanks.|||Howdy

Well you can read all the config values from a table instaed of an ini file. So, you could just copy a table across into a database and use that as a config "module" that can be moved around.

Beyond that, I'd suggest its not straight forward.

Cheers,

SG|||I have tired that as well but once you move the DTS package to a different server the connections in the package still point to the original server. Also, for the DT properties in the package, how to specify the source connection to use for Queries in the package?

Thanks,

V|||Howdy

the problem is that you need to define local logons for the server, so each time you copy the package to a new server, you will need to manually alter the local server logon. No way around this I know of, unless all your servers, userIDs & passwords are the same.

A DT needs 2 connections : ( asuming you are copying data around on one server ) :

1 the source connection - server name & source database
2 the destination conn. - server name & destination database

To create a DT - click the soucre, then hold the control key & clikc the destination. Then while the two conenctions highlighted, do right click & choose Transform Data Task.

Cheers

SG

Dynamic Parameters

Iam programatically passing parameters to the rdl file, Right now I have four parameters but if I want to have a choice of giveing only 2 or more that 6 parameters then do I need to have a different RDl files one with 2 parameters & another with 6 parameters.
I dont think it is feasible.
Even I should have the flexibility to add or remove the number of parameters dynamically. is ther any wayhow can I sole this situation.

Example: Iam displaying employee table of AdventureWorks database where Iam passing
Parameter 1.DepartmentId
Parameter 2.Title
and for Hiredate
iam passing i.e
Parameter 3. startdate &
Parameter 4.enddate

now that I have to give all the values in parameters to view the report if I want to all the employees in deptid = 5 having Title="Buyer" but not restricting them with date then it is not possible.

I hope the Above situation is clear please feel free if U have any queries.

HELP ME Dude's

Regards

You could assign default values to all 6 parameters at report design time. If a parameter value isn't specified at runtime the default value will be used instead. Just write your SQL query to take the default values into account and not use the param as a filter value - it generally looks like (@.Title = TableName.Title OR @. Title IS NULL) AND ...
The one caveat is that default values can sometimes throw off the optimizer and make for long running queries.

Dynamic package configurations

I have a package that will be run by many people, basically that take a flat file from one format to another format. The variables I'd need to change are @.originalFilePath and @.destinationFilePath.

I'm looking at package configurations now, and am wondering what I'm missing. It seems like the configuration settings are static, with an xml file or a sql server table being my most viable options.

What I need is for multiple users to be able to execute the same package with different parameters at the same time. Can anyone guide me in the right direction? I know that through ASP, I could generate XML docs on the fly, but I want to make sure I'm using the best method. What's the best way of going about dynamic configs?
Take a look at DTEXEC and the /SET option. Using that, you can call your pacakge and use SET to override the values of @.originalFilePath and @.destinationFilePath. No configuration file required. It does mean passing the values each time, but given your situation it sounds like that will be the best solution.|||Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003?

I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)
|||

papalarge wrote:

Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003?

I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)

Strictly speaking, .Net 2.0 is required.|||any ideas why I get the error "A reference to 'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll' could not be added. This is not a valid assembly or COM component."?

I have Visual Studio 2003, and .NET 1.1 and 2.0 framework installed on my local machine.
|||Does any of this information help?

http://blogs.msdn.com/michen/default.aspx|||it does help, yeah. the part that says:

Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs. This method also can't be used from .NET 1.1 application, unless it is moved to .NET 2.0 (which should be very easy to do, and in my experience improves the performance as well).

So I guess I need VS 2005 in order to use the object model for SSIS?
|||

papalarge wrote:

it does help, yeah. the part that says:

Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs.

which means you need a license on each machine running the server. were you aware of that?

Dynamic loading of a file

I've been told by Kirk that it is possible to load a file whose metadata is
unknown at design-time by using bulk insert along with a file (not a flat
file) connection.

He didn't elaborate though. Can anyone explain?

-JamieJamie,
Bulk loading to several different tables with known metadata is simple. I don't know how one could load to a table with completely unknown metadata. The file connection works fine with the bulk insert task. If I remember this discussion, you were asking how to put a load into a foreach loop. If you had flat files with several different schema, you could name them accordingly and load into the table with the appropriate schema for that flat file, but without knowing the schema beforehand, there would be no way of generating the table on the destination.
SMO Transfer does something similar, but it's a SQL to SQL solution, ie. one can move a table without knowing it's schema. The reason we can do that is because we can get the schema from the existing table and use it to generate the destination table.
If someone knows how to do this, I'd be interested as well. Sounds like a super task to me.
The question I have is, why would you want to do this? When, I mean, in what circumstance would you do it?
Thanks,
K|||"I don't know how one could load to a table with completely unknown metadata"

Well that's exactly what I'm talking about. I was surprised when we were talking about this before and you suggested it could be done but we obviously got our wires crossed. At least I didn't spend hours trying to find a solution that didn't exist.

Donald actually emailed me about this (i.e. loading a file when the metadata is unknown) a long long time ago because customers were disgruntled to find that it couldn't be done because it CAN be done in DTS. [He wanted to know if Informatica can do it. Answer: No!]

The reason to do it is simple. We already have a DTS solution that can point at a collection of files. All but 1 of those files will be data files but the other one will be a metadata file containing the metadata of the data files. We can read that metadata file, change our DTS pump accordingly, and grab each data file in turn. The only thing that is expected/known beforehand is the structure of the metadata file.
I'm pretty sure alot of other people do similar to this as well.

As far as achieving this in SSIS goes...I think the only solution is a custom component that builds a package on the fly in memory based on the metadata, executes it, and then returns control to the regular package. I remember that Darren was thinking of putting something together around this.
Make sense?

-Jamie|||Another situation...

A guy on the beta NG wanted to build a process that could import data from multiple access databases. The trouble is, the metadata of the MDB (including the tables) is not known beforehand and each MDB was (potentially) different. He just wants to point a package at a folder containing a load of MDBs and say "Import all that stuff, I don't care what's in them".

Disappointingly he is now building a .Net solution to do this.

-Jamie|||If you have a metadata file, something that describes the metadata for a set of flatfiles, this would work. Is this a SQL script file? We don't have a way to ad-hoc discover the schema of a flatfile. We do have some heuristics in the flat file source that try to guess, but that's just what it is, a guess and it's wrong sometimes.
The transfer tasks use a script file. The foundation of which is the transfer provider task. But those are SMO solutions and they use SMO to build the metadata file on the fly (which a SQL script) that the SQL task uses to build the target table and then the pipeline sucks the data from the source table into the target/destination table.
This is the scenario I thought you were talking about. So long as there is a file somewhere that describes the schema of the flatfiles, you can build a package that will do this with a file connection, a bulk insert task, a SQL task in a foreach loop.
Send me a metadata file. If it's a SQL Script file, it's a cinch. If not, what is it?
Thanks,
K|||He should write a custom connection/adapter pair and then market it! :)

K|||

Here is the situation that I am doing.

It's a simple problem, but SSIS cannot handle it.

I want to create an SSIS package, that can dump data from my tables into a file.

The data to dump and the tables to dump is unknown until runtime (I want to be able to set which tables and data to dump using other tables as configuration).

I can build a script file quickly, a metadata file that describes the schema of all the tables that could be exported.

How do I get SSIS to retreive the correct table schema for the table that I want to export into the file?

-rob.cruz

|||

What are you going to do with the files once you've extracted the table data into them?

K

|||Just providing the data to our client who will use the data in their own way. From what I know, they are using it for reports and validation.|||

How many different tables to you have?

Are you doing any transformation on the tables as you export them?

K

|||

no transformations on the tables-

just a straight export

number of tables- that's part of the the problem i am trying to solve- I want the number of tables we can export to be arbitrary- we have probably around 30+ tables that we would want to export, and that can grow or shrink as requested by the client.

I would rather not create a transformation and destination connection for each table- but it may come down to that.

-rob

|||

I was able to do this via the script task!

Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

|||

Very nice. This is always the fallback for everything, but not everyone wants to do it. Now you've gone and done it.

Cool!

|||

Iam trying to do the same thing, difference is I have a flat file source and oledb destination. Iam pretty new to SSIS never done any DTS work. Can you please send me the code how to create a flat file source and insert into a oledb destination table.

so far I was able to create a SSIS package, added a script task, added data flow task, added flat file source, this is where Iam stuck.

any help on this is appreciated. thanks.

|||

Hi Kirk.

Any guidance then on how to use SSIS to export a dynamic collection of tables as flat files. I used a foreach loop and a user variable to set the file name, the first table works fine but then it fails on the second because the column mappings are not being dynamically set in the dataflow task.

Alex

Dynamic loading of a file

I've been told by Kirk that it is possible to load a file whose metadata is
unknown at design-time by using bulk insert along with a file (not a flat
file) connection.

He didn't elaborate though. Can anyone explain?

-JamieJamie,
Bulk loading to several different tables with known metadata is simple. I don't know how one could load to a table with completely unknown metadata. The file connection works fine with the bulk insert task. If I remember this discussion, you were asking how to put a load into a foreach loop. If you had flat files with several different schema, you could name them accordingly and load into the table with the appropriate schema for that flat file, but without knowing the schema beforehand, there would be no way of generating the table on the destination.
SMO Transfer does something similar, but it's a SQL to SQL solution, ie. one can move a table without knowing it's schema. The reason we can do that is because we can get the schema from the existing table and use it to generate the destination table.
If someone knows how to do this, I'd be interested as well. Sounds like a super task to me.
The question I have is, why would you want to do this? When, I mean, in what circumstance would you do it?
Thanks,
K|||"I don't know how one could load to a table with completely unknown metadata"

Well that's exactly what I'm talking about. I was surprised when we were talking about this before and you suggested it could be done but we obviously got our wires crossed. At least I didn't spend hours trying to find a solution that didn't exist.

Donald actually emailed me about this (i.e. loading a file when the metadata is unknown) a long long time ago because customers were disgruntled to find that it couldn't be done because it CAN be done in DTS. [He wanted to know if Informatica can do it. Answer: No!]

The reason to do it is simple. We already have a DTS solution that can point at a collection of files. All but 1 of those files will be data files but the other one will be a metadata file containing the metadata of the data files. We can read that metadata file, change our DTS pump accordingly, and grab each data file in turn. The only thing that is expected/known beforehand is the structure of the metadata file.
I'm pretty sure alot of other people do similar to this as well.

As far as achieving this in SSIS goes...I think the only solution is a custom component that builds a package on the fly in memory based on the metadata, executes it, and then returns control to the regular package. I remember that Darren was thinking of putting something together around this.
Make sense?

-Jamie|||Another situation...

A guy on the beta NG wanted to build a process that could import data from multiple access databases. The trouble is, the metadata of the MDB (including the tables) is not known beforehand and each MDB was (potentially) different. He just wants to point a package at a folder containing a load of MDBs and say "Import all that stuff, I don't care what's in them".

Disappointingly he is now building a .Net solution to do this.

-Jamie|||If you have a metadata file, something that describes the metadata for a set of flatfiles, this would work. Is this a SQL script file? We don't have a way to ad-hoc discover the schema of a flatfile. We do have some heuristics in the flat file source that try to guess, but that's just what it is, a guess and it's wrong sometimes.
The transfer tasks use a script file. The foundation of which is the transfer provider task. But those are SMO solutions and they use SMO to build the metadata file on the fly (which a SQL script) that the SQL task uses to build the target table and then the pipeline sucks the data from the source table into the target/destination table.
This is the scenario I thought you were talking about. So long as there is a file somewhere that describes the schema of the flatfiles, you can build a package that will do this with a file connection, a bulk insert task, a SQL task in a foreach loop.
Send me a metadata file. If it's a SQL Script file, it's a cinch. If not, what is it?
Thanks,
K|||He should write a custom connection/adapter pair and then market it! :)

K|||

Here is the situation that I am doing.

It's a simple problem, but SSIS cannot handle it.

I want to create an SSIS package, that can dump data from my tables into a file.

The data to dump and the tables to dump is unknown until runtime (I want to be able to set which tables and data to dump using other tables as configuration).

I can build a script file quickly, a metadata file that describes the schema of all the tables that could be exported.

How do I get SSIS to retreive the correct table schema for the table that I want to export into the file?

-rob.cruz

|||

What are you going to do with the files once you've extracted the table data into them?

K

|||Just providing the data to our client who will use the data in their own way. From what I know, they are using it for reports and validation.|||

How many different tables to you have?

Are you doing any transformation on the tables as you export them?

K

|||

no transformations on the tables-

just a straight export

number of tables- that's part of the the problem i am trying to solve- I want the number of tables we can export to be arbitrary- we have probably around 30+ tables that we would want to export, and that can grow or shrink as requested by the client.

I would rather not create a transformation and destination connection for each table- but it may come down to that.

-rob

|||

I was able to do this via the script task!

Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

|||

Very nice. This is always the fallback for everything, but not everyone wants to do it. Now you've gone and done it.

Cool!

|||

Iam trying to do the same thing, difference is I have a flat file source and oledb destination. Iam pretty new to SSIS never done any DTS work. Can you please send me the code how to create a flat file source and insert into a oledb destination table.

so far I was able to create a SSIS package, added a script task, added data flow task, added flat file source, this is where Iam stuck.

any help on this is appreciated. thanks.

|||

Hi Kirk.

Any guidance then on how to use SSIS to export a dynamic collection of tables as flat files. I used a foreach loop and a user variable to set the file name, the first table works fine but then it fails on the second because the column mappings are not being dynamically set in the dataflow task.

Alex

sql

Dynamic loading of a file

I've been told by Kirk that it is possible to load a file whose metadata is
unknown at design-time by using bulk insert along with a file (not a flat
file) connection.

He didn't elaborate though. Can anyone explain?

-JamieJamie,
Bulk loading to several different tables with known metadata is simple. I don't know how one could load to a table with completely unknown metadata. The file connection works fine with the bulk insert task. If I remember this discussion, you were asking how to put a load into a foreach loop. If you had flat files with several different schema, you could name them accordingly and load into the table with the appropriate schema for that flat file, but without knowing the schema beforehand, there would be no way of generating the table on the destination.
SMO Transfer does something similar, but it's a SQL to SQL solution, ie. one can move a table without knowing it's schema. The reason we can do that is because we can get the schema from the existing table and use it to generate the destination table.
If someone knows how to do this, I'd be interested as well. Sounds like a super task to me.
The question I have is, why would you want to do this? When, I mean, in what circumstance would you do it?
Thanks,
K|||"I don't know how one could load to a table with completely unknown metadata"

Well that's exactly what I'm talking about. I was surprised when we were talking about this before and you suggested it could be done but we obviously got our wires crossed. At least I didn't spend hours trying to find a solution that didn't exist.

Donald actually emailed me about this (i.e. loading a file when the metadata is unknown) a long long time ago because customers were disgruntled to find that it couldn't be done because it CAN be done in DTS. [He wanted to know if Informatica can do it. Answer: No!]

The reason to do it is simple. We already have a DTS solution that can point at a collection of files. All but 1 of those files will be data files but the other one will be a metadata file containing the metadata of the data files. We can read that metadata file, change our DTS pump accordingly, and grab each data file in turn. The only thing that is expected/known beforehand is the structure of the metadata file.
I'm pretty sure alot of other people do similar to this as well.

As far as achieving this in SSIS goes...I think the only solution is a custom component that builds a package on the fly in memory based on the metadata, executes it, and then returns control to the regular package. I remember that Darren was thinking of putting something together around this.
Make sense?

-Jamie|||Another situation...

A guy on the beta NG wanted to build a process that could import data from multiple access databases. The trouble is, the metadata of the MDB (including the tables) is not known beforehand and each MDB was (potentially) different. He just wants to point a package at a folder containing a load of MDBs and say "Import all that stuff, I don't care what's in them".

Disappointingly he is now building a .Net solution to do this.

-Jamie|||If you have a metadata file, something that describes the metadata for a set of flatfiles, this would work. Is this a SQL script file? We don't have a way to ad-hoc discover the schema of a flatfile. We do have some heuristics in the flat file source that try to guess, but that's just what it is, a guess and it's wrong sometimes.
The transfer tasks use a script file. The foundation of which is the transfer provider task. But those are SMO solutions and they use SMO to build the metadata file on the fly (which a SQL script) that the SQL task uses to build the target table and then the pipeline sucks the data from the source table into the target/destination table.
This is the scenario I thought you were talking about. So long as there is a file somewhere that describes the schema of the flatfiles, you can build a package that will do this with a file connection, a bulk insert task, a SQL task in a foreach loop.
Send me a metadata file. If it's a SQL Script file, it's a cinch. If not, what is it?
Thanks,
K|||He should write a custom connection/adapter pair and then market it! :)

K|||

Here is the situation that I am doing.

It's a simple problem, but SSIS cannot handle it.

I want to create an SSIS package, that can dump data from my tables into a file.

The data to dump and the tables to dump is unknown until runtime (I want to be able to set which tables and data to dump using other tables as configuration).

I can build a script file quickly, a metadata file that describes the schema of all the tables that could be exported.

How do I get SSIS to retreive the correct table schema for the table that I want to export into the file?

-rob.cruz

|||

What are you going to do with the files once you've extracted the table data into them?

K

|||Just providing the data to our client who will use the data in their own way. From what I know, they are using it for reports and validation.|||

How many different tables to you have?

Are you doing any transformation on the tables as you export them?

K

|||

no transformations on the tables-

just a straight export

number of tables- that's part of the the problem i am trying to solve- I want the number of tables we can export to be arbitrary- we have probably around 30+ tables that we would want to export, and that can grow or shrink as requested by the client.

I would rather not create a transformation and destination connection for each table- but it may come down to that.

-rob

|||

I was able to do this via the script task!

Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

|||

Very nice. This is always the fallback for everything, but not everyone wants to do it. Now you've gone and done it.

Cool!

|||

Iam trying to do the same thing, difference is I have a flat file source and oledb destination. Iam pretty new to SSIS never done any DTS work. Can you please send me the code how to create a flat file source and insert into a oledb destination table.

so far I was able to create a SSIS package, added a script task, added data flow task, added flat file source, this is where Iam stuck.

any help on this is appreciated. thanks.

|||

Hi Kirk.

Any guidance then on how to use SSIS to export a dynamic collection of tables as flat files. I used a foreach loop and a user variable to set the file name, the first table works fine but then it fails on the second because the column mappings are not being dynamically set in the dataflow task.

Alex

Dynamic loading of a file

I've been told by Kirk that it is possible to load a file whose metadata is
unknown at design-time by using bulk insert along with a file (not a flat
file) connection.

He didn't elaborate though. Can anyone explain?

-JamieJamie,
Bulk loading to several different tables with known metadata is simple. I don't know how one could load to a table with completely unknown metadata. The file connection works fine with the bulk insert task. If I remember this discussion, you were asking how to put a load into a foreach loop. If you had flat files with several different schema, you could name them accordingly and load into the table with the appropriate schema for that flat file, but without knowing the schema beforehand, there would be no way of generating the table on the destination.
SMO Transfer does something similar, but it's a SQL to SQL solution, ie. one can move a table without knowing it's schema. The reason we can do that is because we can get the schema from the existing table and use it to generate the destination table.
If someone knows how to do this, I'd be interested as well. Sounds like a super task to me.
The question I have is, why would you want to do this? When, I mean, in what circumstance would you do it?
Thanks,
K|||"I don't know how one could load to a table with completely unknown metadata"

Well that's exactly what I'm talking about. I was surprised when we were talking about this before and you suggested it could be done but we obviously got our wires crossed. At least I didn't spend hours trying to find a solution that didn't exist.

Donald actually emailed me about this (i.e. loading a file when the metadata is unknown) a long long time ago because customers were disgruntled to find that it couldn't be done because it CAN be done in DTS. [He wanted to know if Informatica can do it. Answer: No!]

The reason to do it is simple. We already have a DTS solution that can point at a collection of files. All but 1 of those files will be data files but the other one will be a metadata file containing the metadata of the data files. We can read that metadata file, change our DTS pump accordingly, and grab each data file in turn. The only thing that is expected/known beforehand is the structure of the metadata file.
I'm pretty sure alot of other people do similar to this as well.

As far as achieving this in SSIS goes...I think the only solution is a custom component that builds a package on the fly in memory based on the metadata, executes it, and then returns control to the regular package. I remember that Darren was thinking of putting something together around this.
Make sense?

-Jamie|||Another situation...

A guy on the beta NG wanted to build a process that could import data from multiple access databases. The trouble is, the metadata of the MDB (including the tables) is not known beforehand and each MDB was (potentially) different. He just wants to point a package at a folder containing a load of MDBs and say "Import all that stuff, I don't care what's in them".

Disappointingly he is now building a .Net solution to do this.

-Jamie|||If you have a metadata file, something that describes the metadata for a set of flatfiles, this would work. Is this a SQL script file? We don't have a way to ad-hoc discover the schema of a flatfile. We do have some heuristics in the flat file source that try to guess, but that's just what it is, a guess and it's wrong sometimes.
The transfer tasks use a script file. The foundation of which is the transfer provider task. But those are SMO solutions and they use SMO to build the metadata file on the fly (which a SQL script) that the SQL task uses to build the target table and then the pipeline sucks the data from the source table into the target/destination table.
This is the scenario I thought you were talking about. So long as there is a file somewhere that describes the schema of the flatfiles, you can build a package that will do this with a file connection, a bulk insert task, a SQL task in a foreach loop.
Send me a metadata file. If it's a SQL Script file, it's a cinch. If not, what is it?
Thanks,
K|||He should write a custom connection/adapter pair and then market it! :)

K|||

Here is the situation that I am doing.

It's a simple problem, but SSIS cannot handle it.

I want to create an SSIS package, that can dump data from my tables into a file.

The data to dump and the tables to dump is unknown until runtime (I want to be able to set which tables and data to dump using other tables as configuration).

I can build a script file quickly, a metadata file that describes the schema of all the tables that could be exported.

How do I get SSIS to retreive the correct table schema for the table that I want to export into the file?

-rob.cruz

|||

What are you going to do with the files once you've extracted the table data into them?

K

|||Just providing the data to our client who will use the data in their own way. From what I know, they are using it for reports and validation.|||

How many different tables to you have?

Are you doing any transformation on the tables as you export them?

K

|||

no transformations on the tables-

just a straight export

number of tables- that's part of the the problem i am trying to solve- I want the number of tables we can export to be arbitrary- we have probably around 30+ tables that we would want to export, and that can grow or shrink as requested by the client.

I would rather not create a transformation and destination connection for each table- but it may come down to that.

-rob

|||

I was able to do this via the script task!

Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

|||

Very nice. This is always the fallback for everything, but not everyone wants to do it. Now you've gone and done it.

Cool!

|||

Iam trying to do the same thing, difference is I have a flat file source and oledb destination. Iam pretty new to SSIS never done any DTS work. Can you please send me the code how to create a flat file source and insert into a oledb destination table.

so far I was able to create a SSIS package, added a script task, added data flow task, added flat file source, this is where Iam stuck.

any help on this is appreciated. thanks.

|||

Hi Kirk.

Any guidance then on how to use SSIS to export a dynamic collection of tables as flat files. I used a foreach loop and a user variable to set the file name, the first table works fine but then it fails on the second because the column mappings are not being dynamically set in the dataflow task.

Alex

Dynamic loading of a file

I've been told by Kirk that it is possible to load a file whose metadata is
unknown at design-time by using bulk insert along with a file (not a flat
file) connection.

He didn't elaborate though. Can anyone explain?

-JamieJamie,
Bulk loading to several different tables with known metadata is simple. I don't know how one could load to a table with completely unknown metadata. The file connection works fine with the bulk insert task. If I remember this discussion, you were asking how to put a load into a foreach loop. If you had flat files with several different schema, you could name them accordingly and load into the table with the appropriate schema for that flat file, but without knowing the schema beforehand, there would be no way of generating the table on the destination.
SMO Transfer does something similar, but it's a SQL to SQL solution, ie. one can move a table without knowing it's schema. The reason we can do that is because we can get the schema from the existing table and use it to generate the destination table.
If someone knows how to do this, I'd be interested as well. Sounds like a super task to me.
The question I have is, why would you want to do this? When, I mean, in what circumstance would you do it?
Thanks,
K|||"I don't know how one could load to a table with completely unknown metadata"

Well that's exactly what I'm talking about. I was surprised when we were talking about this before and you suggested it could be done but we obviously got our wires crossed. At least I didn't spend hours trying to find a solution that didn't exist.

Donald actually emailed me about this (i.e. loading a file when the metadata is unknown) a long long time ago because customers were disgruntled to find that it couldn't be done because it CAN be done in DTS. [He wanted to know if Informatica can do it. Answer: No!]

The reason to do it is simple. We already have a DTS solution that can point at a collection of files. All but 1 of those files will be data files but the other one will be a metadata file containing the metadata of the data files. We can read that metadata file, change our DTS pump accordingly, and grab each data file in turn. The only thing that is expected/known beforehand is the structure of the metadata file.
I'm pretty sure alot of other people do similar to this as well.

As far as achieving this in SSIS goes...I think the only solution is a custom component that builds a package on the fly in memory based on the metadata, executes it, and then returns control to the regular package. I remember that Darren was thinking of putting something together around this.
Make sense?

-Jamie|||Another situation...

A guy on the beta NG wanted to build a process that could import data from multiple access databases. The trouble is, the metadata of the MDB (including the tables) is not known beforehand and each MDB was (potentially) different. He just wants to point a package at a folder containing a load of MDBs and say "Import all that stuff, I don't care what's in them".

Disappointingly he is now building a .Net solution to do this.

-Jamie|||If you have a metadata file, something that describes the metadata for a set of flatfiles, this would work. Is this a SQL script file? We don't have a way to ad-hoc discover the schema of a flatfile. We do have some heuristics in the flat file source that try to guess, but that's just what it is, a guess and it's wrong sometimes.
The transfer tasks use a script file. The foundation of which is the transfer provider task. But those are SMO solutions and they use SMO to build the metadata file on the fly (which a SQL script) that the SQL task uses to build the target table and then the pipeline sucks the data from the source table into the target/destination table.
This is the scenario I thought you were talking about. So long as there is a file somewhere that describes the schema of the flatfiles, you can build a package that will do this with a file connection, a bulk insert task, a SQL task in a foreach loop.
Send me a metadata file. If it's a SQL Script file, it's a cinch. If not, what is it?
Thanks,
K|||He should write a custom connection/adapter pair and then market it! :)

K|||

Here is the situation that I am doing.

It's a simple problem, but SSIS cannot handle it.

I want to create an SSIS package, that can dump data from my tables into a file.

The data to dump and the tables to dump is unknown until runtime (I want to be able to set which tables and data to dump using other tables as configuration).

I can build a script file quickly, a metadata file that describes the schema of all the tables that could be exported.

How do I get SSIS to retreive the correct table schema for the table that I want to export into the file?

-rob.cruz

|||

What are you going to do with the files once you've extracted the table data into them?

K

|||Just providing the data to our client who will use the data in their own way. From what I know, they are using it for reports and validation.|||

How many different tables to you have?

Are you doing any transformation on the tables as you export them?

K

|||

no transformations on the tables-

just a straight export

number of tables- that's part of the the problem i am trying to solve- I want the number of tables we can export to be arbitrary- we have probably around 30+ tables that we would want to export, and that can grow or shrink as requested by the client.

I would rather not create a transformation and destination connection for each table- but it may come down to that.

-rob

|||

I was able to do this via the script task!

Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

|||

Very nice. This is always the fallback for everything, but not everyone wants to do it. Now you've gone and done it.

Cool!

|||

Iam trying to do the same thing, difference is I have a flat file source and oledb destination. Iam pretty new to SSIS never done any DTS work. Can you please send me the code how to create a flat file source and insert into a oledb destination table.

so far I was able to create a SSIS package, added a script task, added data flow task, added flat file source, this is where Iam stuck.

any help on this is appreciated. thanks.

|||

Hi Kirk.

Any guidance then on how to use SSIS to export a dynamic collection of tables as flat files. I used a foreach loop and a user variable to set the file name, the first table works fine but then it fails on the second because the column mappings are not being dynamically set in the dataflow task.

Alex

Dynamic loading of a file

I've been told by Kirk that it is possible to load a file whose metadata is
unknown at design-time by using bulk insert along with a file (not a flat
file) connection.

He didn't elaborate though. Can anyone explain?

-JamieJamie,
Bulk loading to several different tables with known metadata is simple. I don't know how one could load to a table with completely unknown metadata. The file connection works fine with the bulk insert task. If I remember this discussion, you were asking how to put a load into a foreach loop. If you had flat files with several different schema, you could name them accordingly and load into the table with the appropriate schema for that flat file, but without knowing the schema beforehand, there would be no way of generating the table on the destination.
SMO Transfer does something similar, but it's a SQL to SQL solution, ie. one can move a table without knowing it's schema. The reason we can do that is because we can get the schema from the existing table and use it to generate the destination table.
If someone knows how to do this, I'd be interested as well. Sounds like a super task to me.
The question I have is, why would you want to do this? When, I mean, in what circumstance would you do it?
Thanks,
K|||"I don't know how one could load to a table with completely unknown metadata"

Well that's exactly what I'm talking about. I was surprised when we were talking about this before and you suggested it could be done but we obviously got our wires crossed. At least I didn't spend hours trying to find a solution that didn't exist.

Donald actually emailed me about this (i.e. loading a file when the metadata is unknown) a long long time ago because customers were disgruntled to find that it couldn't be done because it CAN be done in DTS. [He wanted to know if Informatica can do it. Answer: No!]

The reason to do it is simple. We already have a DTS solution that can point at a collection of files. All but 1 of those files will be data files but the other one will be a metadata file containing the metadata of the data files. We can read that metadata file, change our DTS pump accordingly, and grab each data file in turn. The only thing that is expected/known beforehand is the structure of the metadata file.
I'm pretty sure alot of other people do similar to this as well.

As far as achieving this in SSIS goes...I think the only solution is a custom component that builds a package on the fly in memory based on the metadata, executes it, and then returns control to the regular package. I remember that Darren was thinking of putting something together around this.
Make sense?

-Jamie|||Another situation...

A guy on the beta NG wanted to build a process that could import data from multiple access databases. The trouble is, the metadata of the MDB (including the tables) is not known beforehand and each MDB was (potentially) different. He just wants to point a package at a folder containing a load of MDBs and say "Import all that stuff, I don't care what's in them".

Disappointingly he is now building a .Net solution to do this.

-Jamie|||If you have a metadata file, something that describes the metadata for a set of flatfiles, this would work. Is this a SQL script file? We don't have a way to ad-hoc discover the schema of a flatfile. We do have some heuristics in the flat file source that try to guess, but that's just what it is, a guess and it's wrong sometimes.
The transfer tasks use a script file. The foundation of which is the transfer provider task. But those are SMO solutions and they use SMO to build the metadata file on the fly (which a SQL script) that the SQL task uses to build the target table and then the pipeline sucks the data from the source table into the target/destination table.
This is the scenario I thought you were talking about. So long as there is a file somewhere that describes the schema of the flatfiles, you can build a package that will do this with a file connection, a bulk insert task, a SQL task in a foreach loop.
Send me a metadata file. If it's a SQL Script file, it's a cinch. If not, what is it?
Thanks,
K|||He should write a custom connection/adapter pair and then market it! :)

K|||

Here is the situation that I am doing.

It's a simple problem, but SSIS cannot handle it.

I want to create an SSIS package, that can dump data from my tables into a file.

The data to dump and the tables to dump is unknown until runtime (I want to be able to set which tables and data to dump using other tables as configuration).

I can build a script file quickly, a metadata file that describes the schema of all the tables that could be exported.

How do I get SSIS to retreive the correct table schema for the table that I want to export into the file?

-rob.cruz

|||

What are you going to do with the files once you've extracted the table data into them?

K

|||Just providing the data to our client who will use the data in their own way. From what I know, they are using it for reports and validation.|||

How many different tables to you have?

Are you doing any transformation on the tables as you export them?

K

|||

no transformations on the tables-

just a straight export

number of tables- that's part of the the problem i am trying to solve- I want the number of tables we can export to be arbitrary- we have probably around 30+ tables that we would want to export, and that can grow or shrink as requested by the client.

I would rather not create a transformation and destination connection for each table- but it may come down to that.

-rob

|||

I was able to do this via the script task!

Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

|||

Very nice. This is always the fallback for everything, but not everyone wants to do it. Now you've gone and done it.

Cool!

|||

Iam trying to do the same thing, difference is I have a flat file source and oledb destination. Iam pretty new to SSIS never done any DTS work. Can you please send me the code how to create a flat file source and insert into a oledb destination table.

so far I was able to create a SSIS package, added a script task, added data flow task, added flat file source, this is where Iam stuck.

any help on this is appreciated. thanks.

|||

Hi Kirk.

Any guidance then on how to use SSIS to export a dynamic collection of tables as flat files. I used a foreach loop and a user variable to set the file name, the first table works fine but then it fails on the second because the column mappings are not being dynamically set in the dataflow task.

Alex

Dynamic Linking - Crystal XI - Image doesnt change

Im using desktop Crystal XI - connecting to a SQL database via ODBC connection.

Here is the problem: I would like to display a different excel file with each record in the report using dynamic linking.

Each record has a related document (xls) that is stored on a different server than the database. I have access to the folder on the server that contains all of the documents (I connect to this server by mapping a drive via windows explorer). I can manually add a document from the folder but the document doesnt change for each record, it just repeats the document that was added manually.

Ive created a formula field (based on records in the SQL db) to generate the link/document location:

"E:\LCM-" + cstr({mwebDocument.Doc_Ent_ID}, 0, "") + "\" + ({mwebDocument.Doc_File_Name}) + "_" + cstr({mwebDocument.Doc_ID}, 0, "") + "_1.xls"

(example return: E:\LCM-289\KC 2554 Testing Backup NOPAs_9609_1.xls)

I then reference the formula field in the graphic location.

So I know I can access the server/files since I can manually add a file.
I know that the file does display because the manually added file displays for each record (even though it doesnt change).
I know that the link is changing for each record because Ive made it a separate field and can verify its correctness.

Can anyone think of a reason why the object/file is not changing for each record?

Appreciate the help.Why don't u use the formula directly in detail section as a hyperlink..|||Thanks for the response - The major reason for not using a hyperlink is that we would like to distribute this report to people outside of our company (who wouldn't have access to the server/files). This was the initial reason to display the file in the report.

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.