Not really a DB collation problem

 

In trend with CI/CD, and not only because of the trend, we have a TFS build  pipeline and a release pipeline. We deal with on-prems SSIS and most of our deployments mean deploying a SSIS solution and changes to one or more databases, via a Database project deployed as a dacpac package.

The way I work is to test locally from VS, both the ETLs and the database project deployment. 

Recently, however, I tested the ETLs and the DB deployment to a server, as usually. I encountered an error that I initially thought is about the collation of the database: "The project and target databases have different collation settings. Deployment errors might occur." - and deployment errors did occur, as seen below.

Now, since I thought the DB went fine from VS I started to investigate what was different about the collation. 
Banging my head on the wall for a bit, until I read carefully the error message, saying that "Deployment errors might occur". Which means this is not the real error. Oh, yes, so I started to look with a different eye, understood I that the pipeline was set to deploy to another database, than it was all very easy. 

SSIS error VS_NEEDSNEWMETADATA

When deploying an SSIS package on the test server I started to get an error " Data flow task error failed validation and return validation status "VS_NEEDSNEWMETADATA" " I tried to run the SSIS from VS, against the test database and realized I could reproduce the error. I soon realized that the DB I was running against had indeed a different metadata than the local database. For once, there was something useful in SSIS error messages. Fixing it fixed the error.

SSIS and VS 2013: using variables in the Script Task

While trying to use a Project Level Connection Managers in the new SSIS 2016, I realized we need a way to use the connection strings in the package, but didn't have access to them. So I put the Connection Strings into the variables and reuse this further on in the package.

In order to do this, I created two variables (in fact they were created before, this was a migration to SSIS 2016), I created a script task at the beginning of the package,  select the variables that I want to set as ReadWriteVariables,

edit the script and set , in the main function:
Dts.Variables["myVariable1"].Value = Dts.Connections["myConnectionManager1"].ConnectionString;Dts.Variables["myVariable2"].Value = Dts.Connections["myConnectionManager2"].ConnectionString;


That's it!

SSIS Errors

Today, while working on a new ETL in SSIS 2008 (using an old ETL as a model) I stumbled upon an error that was pretty new to me: Error at Data Flow Task [DTS.Pipeline]: input column "Column1" (1234) has lineage ID 1234 that was not previously used in the Data Flow task.

First of all, what is this lineage id? 
According to TechNet Lineage id is "a property of the component or transformation used in the data flow task. It contains an integer value that will work as buffer pointer. Each column in the data flow task will be assigned a lineage id." In fact, a column can have more than one lineage ID, if more transformations are applied on it. Basically every column has an Id pointing to the buffer.
The fix to the problem was to refresh the mapping of the columns. The mapping was not refreshed, so basically a component is waiting for something at a buffer allocation identified by something that was not used before. Reading from the position 1234 in the buffer would yield nothing since no one is writing at that position before.

xp_cmdshell in SQLServer 2014

Today I needed to create a job that moves files on the file system.

despite the recommendetions against xp_cmdshell, I use it because similar jobs do the same, so the SP has to exist on the server anyway. The problem was that running the move command in CMD line worked fine, while running the SQL code returned a "file access denied" exceptions.

Of course, the solution is to give access to the folders to the user that SQL Server runs under. Look at the services in task manager and add the user to the folder.

Time Management. By Brian Tracy

Yet another post about a book. But a very good book, in my opinion.

I got "Time Management" as part of O'Reilly blogger program. I have issues with managing my time, I get a lot of things on my dashboard, but they never get done (maybe I want to do too much). So the book is of great interest to me.

The entire thing about time management is based on the principle of the four Ds: desire, decisiveness, determination, and discipline.
The book is divided into 21 chapters and each of them is a few minutes read but can (and should) generate hours of reflection for someone looking to improve the quality of life.

The book starts with Chapter 1: The Psychology of Time Management, a chapter that explains that we are able to control our time budget. Think "as if" you were already the person you want to be.

Chapter 2: Determine Your Values. This chapter speaks about “Why am I doing what I am doing?”. The meaning and purpose of our actions. Of course, is easier to do things that we love and we are more efficient doing those things.  Also, defining our goals in life is suppose to help tremendously. 


Chapter 3: Think About Your Vision and Mission this chapter refers to Daniel Kahneman’s Thinking, Fast and Slow. The book speaks about the necessity to react fast sometimes and about reflecting to what and why we are doing our actions, thinking more about details. This chapter speaks about not forgetting the goals of our life. Keep the end in mind and examine the methodology. Determine your assumptions and maybe find a better way to accomplish the goal.

Chapter 4: Project Forward, Look Backward 

This chapter talks about thinking. And about the importance to take time to think, plan, dream, and create. Think about the longer term future.

Chapter 5: Make Written Plans 
Define your goals before you start working. Make a clear plan of what you want to accomplish, break it down in smaller steps. Be prepared to review the plan.

Chapter 6: Chart Your Projects
Talks about a project seen as a collection of smaller tasks. And about the importance of checklists. "A checklist consists of a written series of steps, in chronological order, which you create in advance of beginning work in the first place." The author argues that every minute spent in planning saves you 10 minutes in the execution phase. Also he recommends the use of PERT charts and setting clear goals for everyone.

Chapter 7: Create your daily "TO-DO" list

One can see it as a blueprint for the day. he mentions that "the best time to make a list is the night before, so your subconscious mind can work on your list while you sleep." I would add that while working for a big multinational in Italy I had a colleague who was writing down on paper what he wanted to achieve for the next day. And things were going pretty well for him.  The ABCDE Method. The most important word in time management is consequences. A task is important depending on the potential consequences of doing it or not doing it.
The book clearly explains how time wastage (due to bad habits) sabotages careers.
The Not-To-Do List. “No” is the greatest time-saving word in the world of
time management. 
 
Chapter 8: Set Clear Priorities
Ask yourself What is the most valuable use of my time right now?
Is also important to distinguish between important and urgent.

Chapter 9: Stay on Track  
Is also important to distinguish between important and urgent.

Chapter 10: Determine Your Key Result Areas
A KRA is:
- something that you absolutely must to
- 100% under your control
- something that you are responsible for

Furthermore, Brian Tracy dedicates special short chapters to the importance of Delegating to the others (chapter 11), concentrating (chapter 12), overcoming procrastination, creating chunks of time and controlling interruptions, batching your tasks and controlling your phone, reading faster and investing in personal development. I'm not going to take you the pleasure of reading the book by describing each of the chapter

All in one, the book is an easy read, easy to understand and the principles are so simple that one has no excuse not putting them in practice and becoming more efficient. So I would highly recommend it to anyone having problems with time management !

Operation must use an updateable query

I was getting this error while trying to update an Access database (don't ask me why on Earth I use Access - not my decision). After little (fortunately) struggle I realized the DB was read only. It took some time, because another save operation doesn't work, so I was pointing on the wrong direction.

SSIS fails

If you get an error at the running of the Flat File Source SSIS dataflow item
"Task Load {your flat_file} failed" while running a SSIS package (that loads data from a flat file) it might be that you have the flat file opened in Excel. Just for curiosity I tried opening the file in CSVed and it looks like CSVed  doesn't keep it locked, the task runs succesfully.

Hope this is saving you some hair pulling.

Maps in SSRS 2008

Yesterday I was getting an error while trying to run a report that contains a map, saying something like this: [...]maps binding or grouping for layer is not valid[...]. I don't remember exactly the entire error message, but I can reproduce it if one is interested.

This was a little tricky because I started to look at the map layer properties, trying to understand what I did wrong in the RDL. Just to find out that the DataSource was returning more than one row per country, so the poor RS didn't know which row to map.

Think Like a Programmer by V. Anton Spraul, O'Reilly Media

I got this book as part of O'Reilly blogger progam. As a software developer with 14 years of experience myself, I was curious to see what Anton Spraul's book can teach.
You'll not find there the usual pages with code and algorithms that you'll find in many computer programming books. The code you'll find in C++ and is used mostly for exemplification. Is not a book about code, but one about problem solving. Ideas learned can be applied in many other areas of our life.

The book starts with basic logic problems (classic puzzles), the solution to them and the way of thinking that can lead to a solution. Here we learn about being aware of ALL possible actions you could take, creating strategies and simplifying the problem, looking for the most constrained part of the problem, recognizing patterns and analogies. Just as a side note, I'm member of a chess site, where paying members get to try a huge number of tactical problems and this helps a lot in recognizing patterns in the real game. Programming is similar: recognizing patterns and reducing complex problems to known ones.

Then the book goes through specific type of problems in programming, like problems with arrays, pointers and dynamic memory, classes, recursion.

On very interesting chapter is the one about code reusing. It is teaching when is good and when is bad to reuse code, it helps identifying the components and building a "component toolkit" or component knowledge. It makes use of the knowledge acquired in the previous chapters and interestingly teaches which components to use for a specific problem, based on the amount of work, flexibility and maintainability. It not only presents the way to get to a solution, but also comparing two possible solutions to the same problem and choosing the best one.

In all the chapters I didn't type in and compile the code, since I was not interested in coding but in finding patterns of thinking and ways to improve.

The last chapter puts it all together, teaches how to create a development plan by eliminating weaknesses (both coding and design ones) and using the strengths. Also learning a new programming skill is approached systematically and this is approach interesting also for seasoned programmers.

I'll conclude with a quote from the book: "Once you are thinking like a programmer, be proud of your skills. If someone calls you a coder rather than a programmer, say that a well-trained bird could be taught to peck out code—you don’t just write code, you use code to solve problems."

You can find more details about the book and the table of contents on O'Reilly catalog page.