March 12, 2013 at 5:06 pm
We created a bunch of tables some with 40+ columns each and this was a tedious process. We also have several flat files that match the table layouts and we want to import the flat files, using SSIS, into the tables withtout defining the schema of the flat files in SSIS. Is that even possible? (Bottom line: who wants to do the same work -- defining schemas -- twice. Once when we defined the tables and again using the usual out-of-the box approach SSIS seems to adopt. )
TIA,
edm2
March 13, 2013 at 9:36 pm
No, not really. You'll have to configure something, whether it be a Connection Manager in SSIS, a format file or command line for bcp, or some other bulk loading program, that defines the format of those files. SSIS is not a mind reader or a magician 🙂
If the files are delimited you'll have a much easier time of it than if they are fixed-width, but to answer your original question, there is no way around defining the format of the flat file in SSIS within a Flat File Connection Manager if you want to load the files using standard out-of-the box SSIS functionality.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2013 at 11:46 pm
I figured out a way. I used the Import\Export Wizard under SSMS to export the contents of an empty table to an empty file and Saved the resultant package. (Unfortunately this needs to be done one table at a time.Arrgh) Then I copied\pasted the "Flat File Destination" connection, created by the Import\Export wizard, into my new SSIS Package. Done. A bit clumsy but a lot better than parsing each import file.
barkingdog
March 14, 2013 at 12:18 am
Barkingdog (3/13/2013)
I figured out a way. I used the Import\Export Wizard under SSMS to export the contents of an empty table to an empty file and Saved the resultant package. (Unfortunately this needs to be done one table at a time.Arrgh) Then I copied\pasted the "Flat File Destination" connection, created by the Import\Export wizard, into my new SSIS Package. Done. A bit clumsy but a lot better than parsing each import file.barkingdog
Ha, that's clever, but not at all what it sounded like you were wishing SSIS could do. I am assuming these are fixed-width files which is why you were looking for a shortcut? You never said or confirmed.
Just to be clear, for you and future readers alike, you may have found a simpler way to get a Wizard to generate a Flat File Connection Manager you can take with you and use in a package that imports data but you are in no way circumventing the requirement that the flat file structure be defined ahead of time, i.e. you're still satisfying the SSIS requirement that says you must define the flat file structure at design time when using the built-in Flat File Connection Manager in its generic form, you just found a quicker way to generate the Connection Manager.
For next time, most people will spend the time defining the Flat File Connection Manager first and then use the "New" button in the OLE DB Destination Component to have SSIS generate a CREATE TABLE statement for a new table that mimics the incoming file structure.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 7:05 am
A few points:
1. Yes, these are fixed width files. The main annoyance I encountered is that SSIS would not let me define all the connections in one shot. I had to invoke the Wizard several times.
2. I don't like to use the native SSIS facilities to generate tables. They are clumsy to use though they get the job done.
3. Several people (Business Analysts, external programmers) are involved in the larger project. We needed a common communication and construction tool that was accessible to all. We created 10 Excel spreadsheets defining the table schemas ("col name", "width", "Description", etc) and that was used (copy/paste) to create the Sql tables. The way you describe creating a table is fine if the DBA is doing everything on their own. I work with a team that approach is not too useful to me.
edm2
March 14, 2013 at 8:02 am
Barkingdog (3/14/2013)
A few points:1. Yes, these are fixed width files. The main annoyance I encountered is that SSIS would not let me define all the connections in one shot. I had to invoke the Wizard several times.
2. I don't like to use the native SSIS facilities to generate tables. They are clumsy to use though they get the job done.
3. Several people (Business Analysts, external programmers) are involved in the larger project. We needed a common communication and construction tool that was accessible to all. We created 10 Excel spreadsheets defining the table schemas ("col name", "width", "Description", etc) and that was used (copy/paste) to create the Sql tables. The way you describe creating a table is fine if the DBA is doing everything on their own. I work with a team that approach is not too useful to me.
edm2
Not sure I'd be letting the business analysts I know define my schemas! 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 14, 2013 at 8:33 am
I'll have to agree with Phil. Your comment about who was defining tables gave me a bit of a shiver.
re: item 2, I agree the interface is clumsy but it generates SQL that corresponds to the Flat File definition. I usually will not actually allow the package to actually create the table, I just use the UI to get the SQL and then I go to SSMS to polish it up and create it from there.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 6:41 pm
>>> but it generates SQL that corresponds to the Flat File definition.
I didn't know that. Where is it hiding? Must be the .dtsx package....let me look.
edm2
P.S. We don't let the Business Analysts design our schemas but they are quite involved at the Conceptual level and often have read-only access to production physical databases. as they know the data and business relationships better than a dba.
March 14, 2013 at 7:36 pm
In a Data Flow connect a Flat File Source to an OLE DB Destination. Then in the properties of the OLE DB Destination when the type is set to "Table" when you click the "New" button you'll see the SQL SSIS would run to build you a new table on the fly.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 7:54 pm
Thank you very much!
edm2
March 14, 2013 at 11:24 pm
BWAAA-HAAA!!!! opc.three and a lot of others are going to hate me for this.
This is an easy task in T-SQL using xp_CmdShell, dynamic SQL to build "BCP out" commands to build a format file for each table programmatically. Then you don't even need SSIS because you can then use BULK INSERT right from T-SQL using the format files. In fact, even the BULK INSERT commands can be built programmatically with just a smidgin of forethought and planning. It only needs to be done once so hopefully no one gets too cranky about using the wonderful tool known as "xp_CmdShell". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2013 at 9:41 am
Barkingdog (3/14/2013)
Thank you very much!edm2
You're quite welcome!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply