Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Pull "unformatted" data into existing table structure via SSIS? Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 5:06 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:49 AM
Points: 865, Visits: 875
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



Post #1430147
Posted Wednesday, March 13, 2013 9:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1430723
Posted Wednesday, March 13, 2013 11:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:49 AM
Points: 865, Visits: 875
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



Post #1430766
Posted Thursday, March 14, 2013 12:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1430774
Posted Thursday, March 14, 2013 7:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:49 AM
Points: 865, Visits: 875
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



Post #1430942
Posted Thursday, March 14, 2013 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1430984
Posted Thursday, March 14, 2013 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1431012
Posted Thursday, March 14, 2013 6:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:49 AM
Points: 865, Visits: 875
>>> 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.



Post #1431313
Posted Thursday, March 14, 2013 7:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1431320
Posted Thursday, March 14, 2013 7:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:49 AM
Points: 865, Visits: 875
Thank you very much!

edm2



Post #1431328
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse