Hi,
I'm a new user of SQL Server 2005. I have the full version installed. I also have SQL Server Business Integration Dev Studio installed. My OS is Windows XP.
I'm importing a series of 5 flat files into a database on one of the SQL Servers we have. My goal is to get 5 different tables (though perhaps I should do one and add an extra field to distinguish each import) into the database for further analysis.
I tried doing an import via DTS Wizard. There are no column names in the flat file so I defined them during the import process (all 58 of them). When I got to the end, I had an option to save the import process as a SSIS (SQL Server Integration Service) Package on:
SQL SERVER (I don't have permission for this)
or
FILE SYSTEM (did this one)
I saved the Package locally in hopes of being able to go back in, change the source file and destination table of the package and quickly get the other 4 flat files imported.
My problems are:
1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)
2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)
3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)
4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?
5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?
I'm really at a loss after spending a day fruitlessly on it scouring the help files, forums and experimenting around.
Hope somebody can point me in the right direction.
Regards,
Patrick Briggs,
Pasadena, CA
Patrick Briggs wrote:
My problems are:
1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)
You can only run from SQL Server Management Studio if it "knows" about the package. Connect to the SSIS service in SSMS and you'll see that you can tell it about packages stored on the file system and then run them from there.
2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)
You need to add the package to a project. Open VS, start a new SSIS project, and add the package to it. All of this stuff is fairly self-explanatory but if you need a hand - reply here.
3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)
Do you want the package to create the tables or do you want the package to assume that the tables already exist? If the former, then use the Execute SQL Task to issue a CREATE TABLE... If the latter...create the tables in SSMS and don't do anything to the package.
4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?
If you want to run it in BIDS - yes. Create a new project. Right-click on the project name in SOlution Explorer. Select Add->Existing Item... and then browse to you package.
5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?
What are "the basics"? There are books devoted to all the things that leverage BIDS (i.e. SSIS, SSAS, SSRS) but nothing on that only covers BIDS without the context on one of those three.
HTH
-Jamie
|||I just spent some time working out how to do a seemingly simple task.I’m sharing the steps I took to do this in hopes it saves other SQL Server 2005 users (especially newbies like myself) time.
My original question posed on several SQL newsgroups was based on this goal:
I'm importing a series of 5 flat files (all with same file layout) into a database on one of the SQL Servers we have using SQL Server 2005 (SQL Server Management Studio) . My goal is to get 5 different tables.I want to do this without having to redo all the layout criteria 4 additional times.
Below are the steps I followed to get a solution (all done in Microsoft SQL Server Management Studio):
Create the Package (data import)
1) Use the SQL Server Import Export Wizard (equivalent to SQL Server 2000 Data Transfer Wizard) to import your first flat file.At the CHOOSE DATA SOURCE window browse for your file.
2) Under the Advanced tab, you can set your Column attributes (“output column width” or “data type” to name a few).I highlighted all the columns and selected “string [DT_STR]” for data type.To avoid truncation errors, I selected 255 for output column width.You can name the columns whose data you are most concerned with (I did import all the available fields).
3) After choosing a server destination you will have a “SELECT SOURCE TABLES AND VIEWS” window pop up.Under the “Mapping” column you can choose to tweak your mapping further editing in SQL (see Edit SQL button).I didn’t.
4) The “SAVE AND EXECUTE PACKAGE” will pop up.The “Execute Immediately” box should be checked and you should check the “Save SSIS Package” (SQL Server Integration Services).When you do, select “File System” for where to save this import-file-package to.
5) Click OKAY for the Package Protection Level and the “SAVE SSIS PACKAGE” window will appear.Browse for a path on your local computer to save to.
Modify Package (data import) for Next Use
6) In SQL Server Management Studio, browse for the Package and open it.
Preparation for SQL Task – box
7) You should see a screen that shows two boxes (“Preparation for SQL Task”) and (“Data Flow Task”).
8) Right click on the former and select “Edit”.
9) On the “SQL Statement” row, click into the right column and select the “…” box
10) Change the destination table (the table you will create with this package) to a meaningful name and click OK.
11) Click OK for the “SQL Task Editor”
Data Flow Task - box
12) Right click on the “Data Flow Task” box and select “Edit”.
13) Three boxes will appear “SourceConnectionFlatFile”, “Data Conversion 1”, and “Destination - <whatever table name your original data import went to>”.Below them is a section that displays “Connection Managers”
SourceConnectionFlatFile - editing
14) The first thing you will want to do is change the import source to a new flat file.You do this by going below the boxes under the “Connection Managers” window and right clicking on “SourceConnectionFlatFile” and then selecting “Edit”
15) Browse for the new “File Name” and select it.
16) A “Microsoft SQL Server Management Studio” window will pop up asking you if you want to “keep or reset the existing metadata”.The metadata is just your column definitions and choosing “YES” to keep this makes sense if you are doing data imports on files with the same file layout.
17) Still in the “Flat File Connection Manager Editor” window, change the “Connection Manager Name” to something meaningful (I add<_> at the end and then the name of the table the flat file is going to) and click OK.
SourceConnectionFlatFile – box (editing)
18) Right click on the“SourceConnectionFlatFile” box and select “Edit”.
19) Your newly named “Flat File Connection Manager” should appear in select box.
20) Click OK, right click again on the “SourceConnectionFlatFile” box and select “Show Advanced Editor”.
21) Under the “Connections Manager” tab, your newly named “Flat File Connection” should appear (the prior step is necessary for the advanced editor to recognize your change).
22) Under the “Component Properties” tab, on the “Name” row, click into the right column and rename to something meaningful (notice the “Identification String” row description changes too once you click out of the “Name” row)
23) Under the “Column Mappings” tab, just confirm you are mapping your flat file fields (“Available External Columns”) to a destination table’s fields (“Available Output Columns”).
24) Under the “Input and Output Properties” tab you can check in “Flat File Source Output” to make modifications to either your “External Columns” or your “Output Columns” – you shouldn’t need to for a simple import.
((NOTE: any changes you make here would likely need to be consistent with the column properties found under the “Connection Manager Window” for the “SourceConnectionFlatFile” as well as the “Data Conversion 1” box under the “Data Flow Tasks” window, so exercise caution
25) NOTE:This process has worked for me by making my source columns all “string [DT_STR]” data type and the output columns all “Unicode String [DT_WSTR]” data type.
Data Conversion 1 – box (editing)
26) There is nothing you need to do here.By right clicking on the “Data Conversion 1” box and selecting “Edit”, you can see and change the data type of the output columns (the ones in the table your importing the flat file to).There are probably more edits one can do but they’re beyond what I’ve learned.
Destination - <whatever table name your original data import went to> – box (editing)
27) Right click on the “Destination - <whatever table name your original data import went to>” box and select “Show Advanced Editor”.
28) Select the “Component Properties” tab.
29) Select the right column at the “Name” row and change the name to something meaningful (ie. related to the source file name or the table name you’re importing to).
30) Select the right column at the “Identification String” row and it will update to this change.
31) Select the right column at the “OpenRowSet” and change it to the name of the table you are importing your flat file to (this should be consistent with table name under step 10).
32) Click OK
33) Select FILE and select “Save As…” and then give your package a new name that’s meaningful (this will be helpful if you have to rerun the import of the flat file later).
Run (execute) the Revised Package (data import)
34) Go back to SQL Server Management Studio and open the Object Explorer
35) Connect to an “Integration Services” component.This should essentially be a local instance (not sure where it is on the local computer or in SQL Server Management Studio on the local computer).
36) In “Object Explorer” go down to your “Integration Services” object and expand it.
37) Expand “Stored Packages”
38) Right click on “File System” and select “Import Package” and an “IMPORT PACKAGE” window will appear
39) For “Package Location” choose “File System” and then browse for the “Package Path”
40) Click into the “Package Name” and it defaults to your Package’s file name.
41) Click OK and the Package is imported.
42) Right click on the newly imported Package and select “Run Package”
43) An “Execute Package Utility” window appears
44) Select “Execute” and the package runs.
|||Patrick, Can you do this for multiple files in one shot or do you have to go thru these steps separately for every file? Please let me know...I have around 300 flat files with diff. layouts so I am trying to figure out the best way to do this. Thanks!|||I never really tried it for multiple files at once. I am new to SQL Server 2005. I've noticed that as I've used this means of doing multiple imports in my work, it's only time efficient if the files are many columns with varying data types.You bring up a good question which I hope somebody here will try to answer. The steps I outlined are pretty tedious to do file after file.
Regards,
Patrick in Pasadena
|||What's wrong with containing the data flow in a foreach loop that opens each file?|||
I never really tried it for multiple files at once. I am new to SQL Server 2005. I've noticed that as I've used this means of doing multiple imports in my work, it's only time efficient if the files are many columns with varying data types. You bring up a good question which I hope somebody here will try to answer. The steps I outlined are pretty tedious to do file after file.
Regards,
Patrick in Pasadena
I've not followed this thread completely but if you're talking about loading multiple files, this link may help:
Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)
-Jamie
No comments:
Post a Comment