While importing a DDL script recently, I encountered the following gotcha:

My imported script was not formatted correctly as it is when I import the objects from database. To correct the formatting, first I had to deploy the objects to the database. Second, I deleted objects from my project. Finally, by doing a schema comparison between the database and the project, I was able to re-import the database objects. This time, the scripts were both correctly formatted and stripped:

This gotcha did not prevent the project to properly deploy nor did it caused VSTS4DBP to malfunction. I found it more annoying to have scripts formatted differently in my DB project. It is also worth mentioning that this also happens when a column is renamed; if the new name’s length is different than the previous one,the script format will not be adjusted and the columns will be misaligned.
Christian

Labels: VSDB

I recently had to reorganize a database. Basically, I needed create new schemas and to move tables into them. Having a pretty good knowledge of VSVS4DBP, I decided to make usage of its refactoring capability of moving tables to another schema. This article will describe my journey into schema management in VSTS4DBP.

1- The problem : schema creation

The first thing I did was to create a new schema. Pretty strathfoward; I just right clicked under Schema objects –> Database level objects –> Security and selected add new item from the contextual menu. The following dialog box appeared:
 
I named my schema "MySchema" and … nothing! I expected to have the folder created into the solution explorer but the only thing I got was the "MySchema.sql" file that was created in the security folder:

 
I then thought that maybe I was not at the right place to create my schema. I tried to create it from the schema view pane and I ended up with the same result. Still, the newly created schema was visible in the schema view pane though:
 

2- Move a table

I tried then to move a table to the newly created schema. The table has been moved at the right place according to the schema view pane but, still, the folder structure was not created in the solution explorer. And, worst, in the solution explorer the table stayed in its original schema. I could only find its real schema by opening the SQL script as you can see in the following screen captures:

I use refactoring capability of VSTS4DBP to move dbo.ProspetiveBuyer table

The table appears to be moved in schema view pane.

Not in solution explorer though…

3- Deploy the database

I then built and deployed the database project without deploying to the physical database, I simply ask VSTS4DBP to create a SQL script file. From the deployment perspective, everything seemed to be normal. My schema has been created and the table I refactored was in the newly created schema. I then tried to close and reopen solution but it looked like it was impossible to have the folder structure created in either the solution explorer or the windows explorer.

4- Solution: create a new table

I then tried to create a new table in the schema to see where VSTS4DBP would create it. And then voilà! The folder structure got created in windows explorer and appeared in solution explorer.

So here are my conclusions:

1. Adding a new schema does not create the whole structure unless a table is created (not moved) into it.
2. Refactored (moved) tables are only moved in their SQL scripts, not physically in either Solution or Windows explorer. You have to move them to the target folder manually after refactoring.

It is pretty simple once you know it but it took me a lot of time to discover these "features".

Labels: VSDB

In all my applications, I use SQL Server configurations coupled with one indirect configuration. The latter brings me connection string for a connection manager which, in turn, enables my packages to use SQL Server configurations. This is what I call "hybrid" configuration.

A couple weeks ago, I ran into the following problem. I was trying to enter a connection string for a connection manager into my SSIS configuration table and I had this error message:

Clicking on the show technical details icon (rightmost icon at bottom of the error window), led me to this screen:

I highlighted the problem. Basically, the message tells me that the value to be inserted is too long. By default, SSIS creates a configuration table with NVARCHAR(255) columns. The problem I was facing is that when SSIS configures a connection manager connection string, it appends the application name to the connection string. So, my problem was that SSIS tried to insert a value that was longer than 255 characters into the configured value column. In order to get rid of the error, I had two choices:

1. Make the configured value column larger than 255 characters.
2. Remove the application name from the connection string

The application name is an optional parameter that is added to the connection string by SSIS connection manager. It looks like this:

"Application Name=SSIS-StgTemplate-{EE44D17C-AD8C-48E5-B8EB-8197272EE4C0}.\DEV2008.StgDb;"

It is useful when you want to trace SQL connections and distinguish among all active connections on your server. Since the application's name is, for me, superfluous information (I am not running concurrent ETL applications and most of the time, they run on a dedicated server), I decided to use the option #2 and my problem was resolved!