Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pull "unformatted" data into existing table structure via SSIS?


Pull "unformatted" data into existing table structure via SSIS?

Author
Message
Barkingdog
Barkingdog
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 916
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



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
Barkingdog
Barkingdog
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 916
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



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
Barkingdog
Barkingdog
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 916
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



Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19514
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! :-)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
Barkingdog
Barkingdog
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 916
>>> 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.



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
Barkingdog
Barkingdog
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 916
Thank you very much!

edm2



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search