SSIS Script Task Error

(click to enlarge)

Has this ever happened to you? You’re tooling along building an SSIS package. You configure a Script Task, press the F5 key, and BAM! Error!

DTS Script Task has encountered an exception in user code:
Project name: ST_…
Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Not Helpful

Although accurate, this is not a very helpful error. What does it mean?

This error means an SSIS variable or parameter found in the ReadOnlyVariables property or the ReadWriteVariables property of the Script Task does not match the name of a variable or parameter referenced in the .Net code.

You can learn more by taking my short (and inexpensive) course titled SSIS Self-Help!

Learn Power BI From Eugene Meidinger 27 Jun!

I’m excited to announce the availablity of a brand new Enterprise Data & Analytics course titled Power BI: Implementing the Other 90% by Eugene Meidinger (@SQLGene | SqlGene.com)!

Abstract

One of the things Eugene Meidinger found frustrating when he first learned Power BI was all of the behind-the-scenes configuration required to bring Power BI to the enterprise. It was easy to find information about charts and graphs, but difficult to learn about how all the moving parts fit together.

This course focuses on two main areas: data wrangling and administration.

Session 1: Database Theory

When creating a model, it is important to know Power BI is optimized for star schema in particular and filtering/aggregating in general. Within Power BI lies a columnar database that has really good compression. This means Power BI’s model can handle a certain amount of flattening/denormalizing gracefully.

Business users are Power BI’s target demographic. We begin our exploration of Power BI with a review of database fundamentals for business users, covering topics such as:

  • Primary keys
  • Normalization
  • Star schema

Power BI offers two data manipulation languages:

  • Power Query (M)
  • DAX
  • (3 languages if you include R!)

Session 2: Power Query (M)

Power Query is designed for business users. It started as an Excel add-in to assist users familiar with the Excel formula bar. One result: Power Query surfaces an intuitive graphical user itnerface (GUI), but behind the scenes M syntax is generated.

In this session, Eugene discusses and demonstrates tips and tricks for using Power Query to clean and prepare data.

Session 3: DAX

To model and add context to data Power BI users apply DAX. DAX appears deceptively simple, very similar to Excel formulas – but DAX requires thinking in terms of columns and filters, not in terms of rows.

In this session, Eugene scales some of the steeper slopes of the DAX language learning curve.

Session 4: Data Gateways

Many enterprises practice hybrid data management in which some data and services reside in the cloud while other data and services reside on-premises. Data gateways are a way to bridge the Power BI service (in the cloud) with on-premises data.

in this session, Eugene discusses and demonstrates Data Gaterway installation and configuration. Topics include:

  • Scheduling data refreshes
  • Alternative query methods
  • DirectQuery

Eugene authored a Pluralsight course related to this session: Leveraging Timely On-premises Data with Power BI.

Session 5: Licensing and Deployment

In this session, Eugene compares and contrasts Power BI licensing scenarios, including:

  • Power BI Pro
  • Power BI Reporting Server
  • Power BI Premium

Because it can be difficult to keep up with all of the options, Eugene discusses and demonstrates several ways to deploy Power BI dashboards:

  1. Personal workspaces
  2. App Workspaces
  3. Organizational content packs
  4. Publish to web
  5. Sharepoint
  6. Power BI Premium
  7. Power BI Embedded
  8. Power BI report server

Session 6: Security and Auditing

Securing Power BI and the data it surfaces is no longer optional.

In this session, Eugene discusses and demonstrates:

  • Data access management
  • Report access
  • Sharing configuration
  • Row-level security in Power BI and SSAS
  • Auditing via:
    • Unified Audit log for Office 365
    • PowerShell
    • Data Gateway configuration

Conclusion

In conclusion, Eugene says, “Overall I’m pretty proud of the contents. This is the kind of course I wish I had been able to attend 3 years ago.”

About Eugene

Starting out as an accidental DBA and developer, Eugene Meidinger now focuses primarily on BI consulting. He is a Pluralsight course author who has been working with seven years’ SQL Server experience. Eugene holds SQL Server certifications and regularly presents at community events including SQL Saturdays and his local user group.
His current focus is Power BI and related areas.

Register today!

The Next Developing SSIS Data Flows with Labs Course Delivery is 17-18 Jun 2019

I’m excited to announce the next delivery of Developing SSIS Data Flows with Labs will be 17-18 Jun 2019! This two-day course takes a hands-on approach to introduce SSIS Data Flows with a combination of lecture and labs.

About Developing SSIS Data Flows with Labs

Data integration is the foundation of data science, machine learning, artificial intelligence, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals responsible for developing data integration solutions for enterprise-scale Extract, Transform, and Load (ETL) who want to learn more about developing SSIS Data Flows.

You will learn to build data integration with SSIS Data Flows by:

– Learning SSIS Design Patterns.
– Working through hands-on lab exercises.
– Building efficient SSIS Data Flows.

Agenda

1. Introduction to the SSIS Data Flow Task.
2. Designing re-executable loaders with SSIS.
3. Building an Incremental Load design pattern.
4. Data type fundamentals in a real-world scenario.
5. Managing schemata changes to data sources.
6. Intermediate data staging.
7. Deriving to cleanse.
8. Repetition: Iterating file sources.

Space is limited.
Register today!

I hope to see you there.

:{>

Training Feedback

Enterprise Data & Analytics

Here’s what students are saying about the recent Enterprise Data & Analytics free webinars and paid training:

Thank you for presenting the Expert SSIS training sessions Monday and Tuesday this week. I, for one, learned something during each session. Overall, this was one of the top two or three most useful training courses I’ve attended in years.

I loved the way you present and blend it with the demos Andy.

The advanced level of knowledge required kept the event moving at a frantic pace!

I always learn something from Mr. Leonard.

Andy’s training style is to-the-point and experience based, not a recitation of textbook process. He engages the audience with real-world experiences demonstrating useful application of the products he teaches. I find his insights invaluable.

Andy used SSDT demo mode for the exercises, showing exactly what I could expect along with navigation tips and shortcuts using SSDT. I also like the fact that Andy made a point to make sure we, as the audience, was paying attention to the important sections.

Presentation was straight to the point, useful, and with no unneeded fluff. Also, no PowerPoint presentation! Just real-world demos covering the topic being discussed.

Concise and easy to follow. Seems like it would be useful to students of varying skill levels.

I look forward to more of these, Andy. You are a terrific ambassador to the profession.

I’m always grateful when the experts are willing to freely share knowledge.

Yall make an old man blush!

I enjoy delivering training. I find it’s a great way to learn a topic is to deliver training on that topic.

Stay tuned, there are more free webinars on the way! The best way to keep up is to subscribe to my mailing list.

:{>

Join me For Expert SSIS Training!

I’m honored to announce Expert SSIS – a course from Enterprise Data & Analytics!

The next delivery is 01-02 Apr 2019, 9:00 AM – 4:30 PM ET.

Data integration is the foundation of data science, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals responsible for developing, deploying, and managing data integration at enterprise-scale.

You will learn to improve data integration with SSIS by:

  • Building faster data integration.
  • Making data integration execution more manageable.
  • Building data integration faster.

Agenda

  1. SSIS Design Patterns for Performance – how to build SSIS packages that execute and load data faster by tuning SSIS data flows and implementing performance patterns.
  2. SSIS Deployment, Configuration, Execution, and Monitoring – the “Ops” part of DevOps with SSIS using out-of-the-box tools and the latest utilities.
  3. Automation – how to use Business Intelligence Markup Language (Biml) to improve SSIS quality and reduce development time.

I hope to see you there!

PS – Want to Learn More About Azure Data Factory?

Follow Andy Leonard’s SSIS Training page for more information.

Want to Learn More About Azure Data Factory?

From me?

I am honored to announce Fundamentals of Azure Data Factory – a course from Enterprise Data & Analytics!

The next delivery is 04 Mar 2019, 9:00 AM – 4:30 PM ET.

Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – authorblogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates practical Azure Data Factory use cases.

In this course, you’ll learn:

  • The essentials of ADF
  • Developing, testing, scheduling, monitoring, and managing ADF pipelines
  • Lifting and shifting SSIS to ADF SSIS Integration Runtime (Azure-SSIS)
  • ADF design patterns
  • Data Integration Lifecycle Management (DILM) for the cloud and hybrid data integration scenarios

I hope to see you there!

PS – Join me For Expert SSIS Training!

Follow Andy Leonard’s SSIS Training page for more information.

How May I Serve You?

2018 is drawing to a close.

Some readers recently shared requests for more blog posts on certain topics. I thought I’d put the question to my audience (both of you, hi Mom!) to gather your thoughts. Feel free to leave a comment or reach out via email.

Here are some topics I plan to blog about in 2019, Lord willing:

  • SSIS (of course)
  • Azure Data Factory
  • Azure DevOps
  • Data Integration Testing
  • Data Integration Lifecycle Management (DILM)
  • Containers

What do you think? Good list? 

If you were the boss of me, which topics would you like to see – or see more of? Any additions to this list?

Merry Christmas!
*<:{>

Bad Presentations

In just a few short weeks I am attending the PASS Summit 2018 in Seattle. Whenever I attend an event like the Summit or SQL Saturday I attempt to attend presentations of interest to me. I love learning new stuff!

Good and Less-Good

Most of the presentations I attend are good. Some are really good. They are delivered by talented technologists who are also gifted orators. This is an important distinction because:

Technology and communication are entirely different skills.

I’ve watched gifted presenters misrepresent the facts about technology.
I’ve watched gifted technologists fumble demos and stumble over words.

If I have to choose one over the other, I choose great technologists over great presenters. I do so without reservation or hesitation. Is it good to have both? Goodness yes! But – this is important – we don’t always get what we want.

Some Examples of Less-Good

There are categories of bad presentations and bad presenters. Three leap to mind:

  1. Someone who does not know the topic
  2. Someone who is not a good presenter
  3. Someone who is offensive

Presenters Who Do Not Know What They Are Talking About

I wrote recently about a complaint I see leveled at myself and others from time to time: “You do not know what you are doing.” I confess, sometimes that’s a valid complaint about me. While you might find the previous statement an example of humility (or false humility), I prefer to be in the state of not knowing what I am doing. Why? Because it’s an opportunity to learn and I love to learn.

That said, there’s a time and place for everything – including learning. When presenting a class or at a conference for which attendees or their employers paid good money, I strive to share what I have already learned, not what I am learning. Attendees of a free event, even, are paying with their time. So again, I strive for excellence in presentations at free events.

My lovely bride, Christy, attends on average one of my presentations per year. Early on she shared this advice, “Andy, no one likes to watch you troubleshoot.” As an engineer I didn’t even think about it. If something was broken, it needed to be fixed. Period. Pronto. If that meant dropping everything else – in front of a paying audience, even – so be it. Christy’s advice helped me become a better presenter.

I redesigned my talks to be demo-fault-tolerant. I changed my thinking about my presentations so that I am now more mentally prepared for demos to fail. I had something to say, always; I am aware not only that demos will fail. I am prepared for them to fail.

This preparation served me well when, in March 2018 at SQL Saturday Chicago, a drive on my laptop failed just before delivering a presentation that is 90% demos. What did I do? I talked through the demos. A friend approached me afterwards and said, “You looked ready for that!” I was ready for it. Nonetheless I responded (truthfully), “Thank you. I’m going to go sit down for an hour.” Even though I was prepared, it was exhausting.

Good Engineers

I can hear you thinking, “Wait, Andy. Your second item above is ‘Someone who is not a good presenter.’ You titled this corresponding section ‘Good Engineers.’ What gives?” I’m glad you asked.

I am an engineer. If you’ve read my bio you see the word “engineer” there.

I consider this a warning.

Are all good engineers bad communicators? Nope. Many – perhaps most – are, though. Take that last sentence. If English is not your first language I owe you an apology. If English is your first language, I owe you two apologies. It makes perfect sense to me, but any editor who saw that sentence in a manuscript would be compelled to add a comment; correct the sentence; or print the document, take it out back, and physically burn that sentence off the page.

Technology – or engineering – is a skill.
Communication is a skill.
Technology and communication are different skills.

As I stated earlier, I don’t care if the presenter is a bad communicator. There is at least one exception to this rule that I will cover next. But for the most part, I can learn from good technologists who stink at communication. I can learn from good technologists who are brilliant communicators but are having a bad day. How do I know I can learn from these people? Because I have, and do, almost every week.

And so do you.

You may not like the presenter.
You may not enjoy the presentation.
But do you learn? Yep. You do.

An Aside: I Hate Abstract-Writing Contests

I’ve organized community events in the past. I don’t do very much work on community events these days, although I serve as a mascot on a couple leadership committees because of my past adventures in similar endeavors. Occasionally – rarely, I would say – I offer some tidbit that helps. My role these days is mostly to encourage leaders who are on the verge of burnout.

Selecting speakers is an engine of loss. It’s bad when there are more submissions than slots. The effects are amplified if the event is popular. Speaker selection is a fantastic mechanism to irritate and isolate people. For years, in some cases. Everyone who submits believes their submission should be accepted. Otherwise, why submit in the first place? No one likes to hear, “No.” Everyone likes to hear, “Yes.”

I am the same as every other speaker in this regard.
I may be worse than most, even.

Peeves make lousy pets. Knowing this doesn’t stop me from nurturing a handful of pet peeves. One of my pet peeves is speaker selection based solely on the marketing value of the written abstract. I label this practice an “abstract-writing contest” and I believe it is one bane of a successful technical conference.

If you read that last paragraph and began composing a comment that includes a paraphrase of, “Then what would you have us do, Andy? Pick lousy abstracts?!” I feel for you. But I cannot quite reach you. </RedneckSnark>

My response is, “No. I would have you consider more than merely the abstract. And specifically more than its perceived ‘Marketing value.'” Mix things up a bit. Add some variety. Consider the popularity of the speaker’s blog or previous presentations, maybe. Again, there are exceptions (one of which I will address shortly). But here’s my point in a nutshell:

All good presenters deliver good presentations.
Not all good presenters write good abstracts.

Something must take precedence, some attribute must win. If you are organizing an event and / or selecting speakers, you probably don’t have the option of selecting great presenters who will deliver great sessions and who are awesome at writing persuasive marketing abstracts.

Should all presenters strive to write better abstracts? Goodness yes. But unless you’re planning MSIgnite or Build or ReInvent or a TedX, you’re probably not going to be in the position to choose among the great presenters with great abstract-writing skills. Even if this is so, you’re not always going to get it right. They don’t! I’ve been to enough global conferences and seen enough bad presentations to know.

So presenters, strive to write better abstracts.
And selectors, select some presenters who submit poorly-written abstracts. For goodness’ sake: If spelling errors bug you, correct the spelling before you publish anything. That’s not even hard.

</SoapBox>

Regarding Offense

I am of two minds as I begin this section. Here’s why:

  1. There are things I find offensive that, for me, go beyond the pale, crossing the border between acceptable and unacceptable in a public forum meant to convey knowledge to attendees.
  2. There are things I find offensive that I will tolerate in order to gain the knowledge I seek.

Stepping into a conference session, I sometimes do not know what to expect. I may be unfamiliar with the speaker. Perhaps their speaking style is abrasive, or abrasive in my opinion.

Perhaps the speaker uses excessive profanity. I can hear you thinking, “Who gets to define excessive, Andy?” The attendees get to define excessive. As an attendee, I get to define excessive.

I read about a presentation a few years ago where the speaker created a pornographic demonstration. Many in attendance found the presentation offensive, most found it unprofessional. I hope the speaker – obviously a talented technologist – learned from this mistake.

These are examples of the first type of offense, those I believe are unacceptable.

Perhaps the speaker mentions politics in jest and I disagree with their politics, so I feel belittled when those surrounding me in the room laugh at the joke. I may feel they’re laughing at me. They are certainly laughing at people like me.

Perhaps the speaker jokes about the less-sophisticated in our culture. I’ve heard the term “hick” and “redneck” bandied about, for example – terms with which I personally identify. I have people in Appalachia, from whence my family comes. “Trailer trash” was a term I heard years ago in a presentation. My teenage years were spent living in a mobile home. In a trailer.

Do these references offend me?
They do.

Do I tolerate these offenses?
I do.

You may read that last section and think something along the lines of, “Well, they must not offend you that much if you tolerate them, Andy.” To which I respond, “That’s not your call.” You do not get to make such an assertion. You lack the ability to see inside my mind and inside my heart, so you cannot accurately render judgment as to what goes on there. Further, you do not dictate how I think or choose to respond, publicly or privately. You do not have that right of imposition; not over me.

These are examples of the second type of offense; those I choose to tolerate.

Waxing Philosophical…

How should we then live?” is a question posed by theologian, philosopher, and pastor Francis A. Schaeffer – it’s the title of one of his books. It’s a fair question – especially in an age that considers outrage a virtue. here are, I believe, some truths:

Offense can be intended.
Offense can be unintended.
Regardless of the motive of the offender, offense must be taken in order for the offended to be, well, offended.

I believe motive counts.

Does motive excuse the offender then? Not completely, in my opinion. That said, unintended offense deserves a more mitigated response than intended offense – again, in my humble opinion.

i write this as someone who has offended others unintentionally.
I write this as someone who has been offended intentionally and unintentionally.

When I have offended others, I most often apologized. I’ve learned the earlier the apology is offered, the better. I’ve failed – sometimes for years – to apologize for some offenses I’ve caused. This is partially due to ignorance on my part – me not realizing until later that I owed someone an apology. Sometimes I’ve just been stubborn (a virtue for an engineer… which is one reason I warn people that I am an engineer…).

I will likely offend people in the future. Offending people is not my goal and certainly not my intention.

I will most likely offend when trying to make a joke – as others have offended me while trying to make a joke. This has happened to me in the past. It’s cost me relationships, both professionally and personally.

In these cases, I bear the loss and I am profoundly sorry.

Please read and understand this: There are principles – and a Person – in which (and in Whom) I believe. I value my faith more than I eschew offense. My weak, flawed, and hypocritical following of Christ will offend a handful of people, some of whom also follow Christ. Knowing this does not deter, defer, or lessen my beliefs or my commitment thereto. If this offends you, you will have to decide how you respond. If my faith offends you, I believe you will have to also respond at least once more in the future. So if my faith offends you, I pray (and I never say or write the words “I pray” without actually praying) that you take care in your current response.

I share more about my faith in the Personal section of the About Andy page.

Peace.