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 file name for Bulk Insert

SQL Server 2K

OK, I'm probably being a bone-head here and am clearly in over my head but how do you (or can you?) set up a Bulk Insert to take a dynamic path/file name?

What I want to do is pass in the path and file name from an external process to a stored procedure that bulk inserts the content of the file and then does some other routines on it. I haven't had any luck getting Bulk Insert to run if the path/file name is not hard-coded in the sproc as a string.

The point is to have a master routine that can exercise the process for several different customers and use meta data in a table to inform what file to bulk insert.

Any suggestions?

Thanks!Just put any text file in that location to run this test...

Oh, and learn to love bcp...

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

DECLARE @.fn varchar(8000)
SELECT @.fn = 'd:\test.txt'

/* Will Fail
BULK INSERT Northwind.dbo.myTable99
FROM @.fn
*/

DECLARE @.cmd varchar(8000)
SELECT @.cmd = 'bcp Northwind.dbo.myTable99 IN ' + @.fn + ' -c -T -S' + @.@.SERVERNAME
EXEC master..xp_cmdshell @.cmd

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Thanks Brett!

If I have a chance I'll give it a try. We're supposed to run 30 files through this process this weekend and it would be awfully nice to have a little automation on our side.|||Just a little automation?

Some flygirl

http://weblogs.sqlteam.com/brettk/archive/2005/06/28/6895.aspx

Then just use a cursor on the "dos dir" table and use my bcp I gave you...I think I did an archiving thing somewhere...|||Brett, really, I'm trying to love bcp. However, it's a challenging relationship due to a terrible lack of communication. He just won't open up and talk to me. All this stonewalling is making me insecure and I may have to return to previous relationships. But I'd really like to work it out...

Here's what I've got:

-- This Works
BULK INSERT MyDatabase..MyView FROM 'MyPath\MyFile.txt'
WITH (
DATAFILETYPE = 'widechar',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

-- This doesn't work
DECLARE @.cmd varchar (8000)
DECLARE @.FName varchar (100)
DECLARE @.FPath varchar (500)

SELECT @.FName = 'MyFile.txt'
SELECT @.FPath = 'MyPath\'

SELECT @.cmd = 'bcp MyDatabase..MyView IN "' + @.FPath + @.FName + '" -w -T -S' +@.@.ServerName

EXEC master..xp_cmdshell @.cmd

The bcp chunk gives me:

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 53046
NULL

And, indeed, it copied no rows. I've tried passing in a -t\| because the files are pipe delimited but then it won't even run. The row terminator is the default and SQL Server tells me that the file is widechar.

Did I miss something?

Also, I do have the archive piece. However, when I try to run it I get annoying access errors. It may work if I run it off of Prod with the correct login but I haven't gotten that far with all of this yet.|||Got the archive piece working by giving the world perms on my test setup. It looks good, thanks a bunch.|||Why are you using -w btw?

What's the file look like? Looks like your file is pipe delimited, at least based on your BULK INSERT statement.

You need to specify that using -t| and probaly you need to use -c as well

It actually might be -t"|"...I'll have to play around with it...

BUT! BULK INSERT is ok as well...as long as you're not using DTS.

Using bcp or BULK INSERT you can automate stuff easier usiong dynamic SQL. DTS would be such a pain

Did you get all your files loaded?|||THANK YOU, THANK YOU! THANK YOU! And where do I send the margaritas?

OK, all it wanted was the double quotes around the pipe. DOH! Guess that was why it wouldn't run when I included the -t| before. It would be so lovely if it could at least give me a hint at where the problem is. I studied the info from the books online extensively and they don't mention or give examples of what to do if it is not tab delimited other than that you could follow -t with another parameter to override the default. I'm afraid I'm a bit of a dolt when it comes to running code at the DOS prompt. I stricktly avoided coding at all when that was the main method. However the more I write code, the more I end up there.

SELECT @.cmd = 'bcp MyDB..MyView IN "' + @.FPath + @.FName + '" -w -t"|" -T -S' + @.@.ServerName

With that change I successfully copied 66123 rows to my destination table via the view. This is perfect!

FYI, I'm using -w because Query Analyzer politely told me that I should be using widechar the first time I ran the Bulk Insert. Communication is a wonderful thing.

This will be great to have this working. It will mean taking all of this mess to a much better level of automation. Thank you so much for the hand-holding.

Whohooo! This made my day!|||Just curious...did you combine the dos sproc together with bcp?|||I was just celebrating running a single file through bcp. However I will look at the other script because we have processes here that would happily use that.

BTW the 30 files got combined into one--saving only the last row sent for any SSN. Then I ran system updates only once to catch up a database to the last 30 weeks of employee rosters. It was one of those 'write code to update a gazillion tables' on a database I wasn't familiar with. Got the job on Tuesday afternoon and it absolutely had to be done by Friday COB. Thursday I found out that updates needed to be processed for 30 weeks of files. The idea of running system updates 30 times was not a good one especailly on totally raw code that could end up duping records 30 times or something painful like that.

I can usually find ways to get my work done on time but I really appreciate the chance to improve HOW I'm getting it done. You've been a big help.

Thanks!