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
No comments:
Post a Comment