Voice of the DBA

Category: Blog

  • Importing Flat Files in SSMS 17.x Works

    One of the more annoying things I’ve found in SSIS/SSMS is trying to import CSV files for some quick analysis. For some reason I constantly seem to get files that the import wizard keeps having issues. On top of that, the defaults are annoying. When I need to get a CSV in, I select a Flat File source, but the filter for files always defaults to text files. Then there are more drivers and choices that make this complex.

    Recently I was looking to load a file and noticed a new option: Import from Flat File. I decided to try it and was pleasantly surprised.

    2018-08-27 09_52_03-

    Updating a Database

    I have a database of some baseball statistics, but wanted a few updates. I started using this wizard to quickly update about 10 tables. Here’s how this works. When you select the option, a wizard starts with this screen.

    2018-08-27 09_54_42-Microsoft Edge

    There’s no reason to look at this more than once, so check the “Do not show” box, as I did. Click Next.

    Once you do that, you need to pick the source of your data. There’s a Browse button, and when you pick the file, you get a filter for Data files, which are .csv and .txt files. The file you pick will be used for the table name in the dbo schema, but those can be changed in the text boxes.

    2018-08-27 09_55_39-Microsoft Edge

    Note that this wizard only works with new tables, so if your plan is to update another table, you need to stage this data in its own space first.

    The next step in the wizard is a preview of the data. This is handy, and for me I glance to get an idea of what the data is. This is useful and you can use the “Previous” button at the bottom to get back here from the next screen.

    2018-08-27 09_56_46-Microsoft Edge

    This next screen is the reason I love this wizard. I can get the data types and the PK for the table. This designer makes it easy to import the data. I can fix the table, which is nice. In a few of these files, I want to change from nvarchar to smallint for data, and allow nulls for some fields. Plus, being able to set the PK here is great.

    2018-08-27 09_58_14-Microsoft Edge

    The next step is a summary. I usually quickly click the Next button to import data.

    2018-08-27 09_58_21-Microsoft Edge

    If things work well, you get a green checkmark here. If not, you can go back and change settings, like adjust the PK or allow nulls. I even go back twice to preview data sometimes.

    2018-08-27 09_58_28-Microsoft Edge

    One note here. If you go back, the table exists, and you’ll need to drop it.

    If you care, you can get the baseball statistics from here: http://www.seanlahman.com/baseball-archive/statistics/

  • SQL in the City Summits–Coming to New York, London, and Chicago

    I love New York. Every time I get to travel to the city, I get excited and look forward to the trip. I was able to spend a night in Brooklyn earlier this year, touring a college with my daughter. Now I’m very lucky to get a second trip this year, with Redgate for a SQL in the City Summit.

    Social all 1200x628

    We have 3 live Summit events coming in October to a few cities: New York, London, and Chicago. These are in person events where we have presentations scheduled for customers and potential customers. These are thought provoking talks and interactive workshops, aimed a C-level and senior staff. If you’re got someone in your organization that might be interested, pass this link along: https://www.red-gate.com/hub/events/redgate-events/sqlinthecity-summit/

    We have Redgate speakers and expert guests, including Bob Ward of Microsoft. If you have the chance to go, contact your account rep today and get a code to register. These are paid events, though a limited number of free tickets are available.

    The schedule is:

    This will be a busy month of travel for me, with the PASS Summit coming the week after Chicago. It should be an inspirational and exciting set of events, and I hope to see some of your at one of these Summits.

  • Redgate University is Growing

    There isn’t a degree to be gained, but you can get a lot of information about Redgate products at Redgate University. This is the place to learn how to best use the products. This is a work in progress and we’ve started with a few of the more popular items, but we should grow the list over time.

    I’m in a few of the SQL Monitor classes, helping to teach them. The classes are designed to be short and focused on one topic. If you watch them in order, you’ll get a flow of how to use the product and what features are available.

    This is a great idea to help someone bone up on their tools and learn to get the most out of them. Even if you use some of our products regularly, you might want to skip through the modules and see what we cover. I’m sure there are things for you to learn.

    I think I’m on the hook to record a few later this year, but we’ll see how this goes. These are quick to record, but there is a lot of prep to get material ready.

    If there are things you’d like to see, either within one of the courses up there or in another product, let us know. The email for ideas is: university@red-gate.com

  • Discussing Database DevOps with Gene Kim

    I’m really excited for this webinar. Register today for the Sept 25 webinar  to learn more about the 2018 State of DevOps report.

    I read The Phoenix Project a few years back and I’ve been following Gene Kim as one of the main researchers and advocates of DevOps ever since. I think his work is amazing and I’ve enjoyed watching him present online during conferences and webinars. I never thought I would get the chance to talk one on one, much less host a webinar.

    This year’s State of DevOps report, from DevOps Research and Assessment (DORA), sponsored by Google Cloud, is near and dear to my heart. For the first time, there is a section on databases. Redgate helped sponsor the report as well and was able to get Gene Kim to do a webinar to discuss the entire report, but with a focus on databases.

    At least, that’s my focus. I’ll get the chance to ask Gene questions as well, mostly focused on the data aspect of software delivery, which I think is too often left out. Too many software developers are worried about the database and haven’t included it in the their DevOps world. That’s changing, and join me to see how.

    Don’t forget to register and I’ll see you on the 25th.