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 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 Flat File Destination Name!

Hi,

I am trying to access from OLE DB source. And based on one of the columns, I need to write the data to a Flat File Destination.

For Example,

CustID, ProductID, Product Name, Product Description

Say I am going to write to a different Flat File for every product. So if there are 10 products in the data. There should be 10 Flat Files. Also the file name should include the Product Name And Product ID.

It is being done in a single Data Flow Task.

Right now the Property Expression for the File Name is which is not working)

Code Snippet

@.DestFolder + [Data Conversion].ProductID + @.TodaysDate + ".txt"

The ProductIDs are in the ascending order. Any help or guidance?

Thanks

-Leo

You can't do that in one data flow unless you want to split it out to a separate destination (and connection manager) for each product. That might be okay for three or four products, but not if you have a bunch.

What you should do instead is set up a ForEach loop based on a the results of a SELECT DISTINCT ProductId query. Then execute your data flow once for each ProductId, customizing both the Source query and the destination connection manager with your ProductId.
|||

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

|||You might be able to use the Export Column transform. That transform writes out a file per row based on one column containing the filename and another column containing the data.|||

Correct me if I am wrong, Ted, but the Export Column transform is used when you have a binary field in your data flow that you want to persist to a file. That doesn't seem to be the case for Leo's data.

Leo, you can partition the file by rows, but what Jay is suggesting would be simpler and perform better. If you use a For Each Loop, you would get a list of all product IDs, then execute a data flow inside the loop for each product ID. Using expressions, you can alter both the OLE DB Source query and the destination file connection string for each iteration of the loop. You wouldn't ever read a destination file - you would only be writing rows to them. And you would only process each row once.

Here's a few examples of using ForEach loops:

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

http://www.sqlis.com/55.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx

and a search that might have some more:

http://search.live.com/results.aspx?q=foreach+&form=QBRE&q1=macro%3Ajamiet.ssis

|||The Export Column should work with text and ntext in addition to binary -- the column containing the file data just needs to be either a character or binary lob column. This is just another option for Leo with a different approach, requiring the data and file columns to be derived, which he might be able to do since he has already derived the filename column in his original post.|||

New Leo wrote:

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

Leo,

Yes you can do this. I've covered this exact problem here:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

You won't be able to do it in a single data-flow tho.

-Jamie

Dynamic Destination Flat File Loading

Here's what I want to do -

Dynamically load a flat file from a dynamic source table-

The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)

Would this be enough to dynamically create the destination flat file? If so, how do I do it?

Thanks

-rob

The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||

This is actually the answer!

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

Dynamic Destination Flat File Loading

Here's what I want to do -

Dynamically load a flat file from a dynamic source table-

The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)

Would this be enough to dynamically create the destination flat file? If so, how do I do it?

Thanks

-rob

The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||

This is actually the answer!

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

Dynamic Destination Flat File Loading

Here's what I want to do -

Dynamically load a flat file from a dynamic source table-

The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)

Would this be enough to dynamically create the destination flat file? If so, how do I do it?

Thanks

-rob

The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||

This is actually the answer!

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

Dynamic connection manager file name

Hi There

This should be an easy one i hope.

I need to dynamically change the file name of my destination flat file connection everytime the package runs, obviously i do not want to edit the config file everytime.

I think the best way to do this is by a script taks that sets the connection manager filename property with a variable that i dynamically populate.

Is this the right way?

I just want to be sure that there is not an easier or better way to do this?

Thanx

Yes, that sounds like a good approach. Don't use a script task though (because its not possible to do it with a script task), use a property expression (http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=).

You can parameterise the package execution by passing values in from the command-line using the /SET option.

-Jamie

|||Thats is exactly what i was looking for, thanx a million Jamie|||Sorry to be daft, but how to do you get to the setting, when I click on the flat file source, I pull up the connection manager, which doesn't have an expressions section like the others. Could someone give me a general walk through as to how I can set the expressions property for the flat file source file name?

Thank you
|||If you select the connection for the flat file in the connection managers section of the package, the properties window will display for the properties for that connection. One of the properties is Expressions. You can probably figure it out from there.|||Right...Thanks a lot

Dynamic connection manager file name

Hi There

This should be an easy one i hope.

I need to dynamically change the file name of my destination flat file connection everytime the package runs, obviously i do not want to edit the config file everytime.

I think the best way to do this is by a script taks that sets the connection manager filename property with a variable that i dynamically populate.

Is this the right way?

I just want to be sure that there is not an easier or better way to do this?

Thanx

Yes, that sounds like a good approach. Don't use a script task though (because its not possible to do it with a script task), use a property expression (http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=).

You can parameterise the package execution by passing values in from the command-line using the /SET option.

-Jamie

|||Thats is exactly what i was looking for, thanx a million Jamie|||Sorry to be daft, but how to do you get to the setting, when I click on the flat file source, I pull up the connection manager, which doesn't have an expressions section like the others. Could someone give me a general walk through as to how I can set the expressions property for the flat file source file name?

Thank you|||If you select the connection for the flat file in the connection managers section of the package, the properties window will display for the properties for that connection. One of the properties is Expressions. You can probably figure it out from there.|||Right...Thanks a lot

Dynamic columns for Flat File destination?

I have a database app, and we're implementing various data export features using SSIS.

Basically, it's a couple of straight extracts of various recordsets/views, etc. to CSV (flat files) from our SQL Server 2005 database, so I'm creating an SSIS package for each of these datasets.

So far, so good, but my problem comes here: My requirements call for users to select from a list of available columns the fields that they want to include in their exported file. Then, the package should run, but only output the columns specified by the user.

Does anyone have any idea as to the best way to accomplish this? To recap, at design time, I know which columns the users will have to choose from, but at run time, they will specify the columns to export to the flat file.

Any help or guidance here is greatly appreciated

You will need to create the package programatically in order to acheive this.

http://msdn2.microsoft.com/en-us/library/ms345167.aspx is a good place to start.

Donald

|||

Thanks Donald... I have one more thing to ask. Is it possible to create and execute a package from scratch within a Script Task?

I really want to avoid scripting this stuff outside of the SSIS package (ie, in a VB.NET assembly), because my solution architecture depends on encapsulating all of the logic for SSIS stuff within the package.

If it's not possible from within a script task, could there possibly be another way to achieve this while preserving encapsulation of the logic within the SSIS package?

|||

Yes it is possible to do it in a script task. the code is exactly the same as if you were doing it outside of SSIS (as long as you were using VB.Net of course :)

The best example I have seen so far is here: http://www.gotdotnet.com/codegallery/codegallery.aspx?id=042f5bda-78c6-4c94-a68e-c1917b036db3 Click on "Create Packages Dynamically" in the bottom right hand corner.

-Jamie

|||

Hi Jamie,

again, thanks for the reply... While that code is indeed a fine example of creating packages programmatically, there's a bit of a problem with trying to replicate it inside of SSIS.

That code sample (as well as pretty much all others I've found for building packages programatically) imports the Microsoft.SqlServer.Dts.Runtime.Wrapper and the rosoft.SqlServer.Dts.Pipeline.Wrapper namespaces. Apparently neither of these namespaces are available from within a SSIS script task. The VSA IDE won't let me add references to the assemblies containing these namespaces.

Is there something else I'm missing?

|||

It is not possible to create packages or new objects within packages using SSIS.

You may be able to build the functionality into an assembly which could be called from a script - but you'll need to deploy the assembly separately.

Donald

|||

Oops, I didn't know that! Sorry J Nail!

If I were you I would build it innto a custom task. Its not that difficult - not that much harder then doing it in the script task.

-Jamie