SSIS – Unexpected error from external database driver – MS Jet database engine

A recent Windows 2012 OS roll up patch causes the execution of any SSIS package to fail under the following conditions:
1. The package is using Microsoft.Jet.OLEDB.4.0
2. The Package is executed from a SQL job.
3. The OS is Windows 2012 on which the SQL is installed.

Snapshot of the error message:

 

 

 

 

 

MS has acknowledged the issue. Below link with details and workaround.

https://support.microsoft.com/en-us/help/4041692/windows-server-2012-update-kb4041692.

Try and try again – SSMS

Came across this very interesting article which talks about connection resiliency logic in the SQL Server management studio. What this basically does is try to re-connect in case of a time out after sleeping for 10 seconds. The default value for retry is 1 and default interval is 10 seconds. This article from
https://blogs.msdn.microsoft.com/sqlcat/2017/04/06/try-and-try-again-not-always-a-good-idea-at-least-not-for-ssms/

White Paper on connection resiliency

https://msdn.microsoft.com/en-us/library/dn632678.aspx

I hope this article was useful. Have a good day ahead.
Bodhisatya

 

 

End of Days – CodePlex

Microsoft will be shutting down codeplex come December. The official date seems to be the 15th of December 2017. All source code has been moved to GitHub. Codeplex was started in 2006 and was a repository for any developer who wanted to share code with the community.

Link below for more details..

https://blogs.msdn.microsoft.com/bharry/2017/03/31/shutting-down-codeplex/

 

I hope this article was useful. Have a good day ahead.
Bodhisatya

SQL Server 2016 – End of an Era – So long AdventureWorks..Welcome World Wide Importers

End of an era if I should say so. It all started with SQL Server 2005 with a database named AdventureWorks. A sample database to help learn different aspects of the product. Take a bow AdventureWorks.

Welcome [WideWorldImporters]!!! This is a new database developed by MS targeting SQL server 2016 and Azure. Links below for download. Loads of goodies to download.

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

 

I hope this article was useful. Have a good day ahead.
Bodhisatya

SQL Server – How to compare execution plans

One of the challenges for DBAs and Developers was to compare execution plans effectively. In the latest SSMS released by MS we have an option of comparing execution plans in a better readable format. This short article will explain that.

Step 1: Download the latest SSMS from MS website or If you have installed SQL server 2016 then this feature will be available.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Step 2: Install the downloaded package

Step 3: Execute a query and save the execution plan somewhere.

Step 4: Execute another query and wait till the plan is displayed.

Step 5: Right click on the displayed plan and choose the following option highlighted.

This will ask for the saved execution plan. Choose the saved plan.

Step 6: Click on open
The actions opens 2 windows which shows the difference between the 2 execution plans as depicted below. This is a very handy feature when we need to compare the execution plans for a SP or query pre and post change to gauge the effect in terms of performance.

 

 

 

I hope this article was useful. Have a good day ahead.
Bodhisatya

SQL Server 2012,2014,2016 – When a CU patch can break an existing functionality!!!

Recently MS released the following patches for SQL Server. The product team has identified that the patches can be potential CDC functionality breaker. The link below for the note from the product team. Worth a read before applying them on production boxes specially if CDC is active.

  • SQL 2012 SP3 CU8
  • SQL 2014 SP1 CU10
  • SQL 2014 SP2 CU4
  • SQL 2016 RTM CU5
  • SQL 2016 SP1 CU2

https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

 

I hope this article was useful. Have a good day ahead.
Bodhisatya

 

How To Flatten Xml Data in SQL Server

In this article we will see how we can flatten a xml data and make it more readable. Let us understand the scenario first and then dive into the solution. To understand the scenario we will create a xml file. Load the data in the file into a table in SQL Server.

Step 1:  Create a xml file and store it in the C drive. Below is the content of the file

<?xml version="1.0" ?>
<Root>
<Person>
<Name>John</Name>
<Age>30</Age>
<Hobbies>
<Hobby>PhotoGraphy</Hobby>
<Hobby>Driving</Hobby>
</Hobbies>
</Person>
<Person>
<Name>Wayne</Name>
<Age>21</Age>
<Hobbies>
<Hobby>Fishing</Hobby>
<Hobby>Cooking</Hobby>
</Hobbies>
</Person>
</Root>

Step 2 : Save the file as XmlSource.xml on the C drive. You can choose a different location also.

Step 3: Run the following script and create a table which will store the xml data.

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'XmlTable')
DROP TABLE XmlTable
go
CREATE TABLE XmlTable
(
XmLId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
XmlData XML NOT NULL
)

Step 4: Run the below script to insert the contents of the xml file into the table

INSERT INTO XmlTable(XmlData)
SELECT * FROM OPENROWSET(BULK 'C:\XmlSource.xml', SINGLE_BLOB)
AS ImportSource
GO

Step 5: Check if the data has been inserted correctly or not.

xml1

 

If we observe we will see that the contents of the xmlData row are in xml format. So how do we make it something like below
xml2

Here you go. The below query will make it happen

SELECT pref.value('(Name/text())[1]', 'varchar(50)') AS Name ,
pref.value('(Age/text())[1]', 'varchar(50)') AS Age,
pref.value('(Hobbies/Hobby/text())[1]', 'varchar(50)') AS Hobby1,
pref.value('(Hobbies/Hobby/text())[2]', 'varchar(50)') AS Hobby2 
FROM
XmlTable
CROSS APPLY
XmlData.nodes('//Person') AS People ( pref )

The quick way to extract the xml data is to use the CROSS APPLY operator which uses a function
against each row and then adds the data to the result set. We then combine this with a function
that can be executed on the XML data type called NODES

 

 


SQL Server – Integration Service – How To Extract Images From a SQL Server Table To a Folder Using SSIS

In this article we will go through how we can extract images from a table in SQL Server and copy it on a folder. To achieve this we need to use SQL Server Integration Service. Let’s go over the process step by step.

Step 1: Create a new SSIS project in BIDS
IE1

Step 2: Drag and drop the Data Flow Task from the SSIS Toolbox to the design surface

IE2

Step 3: Go to the Data Flow tab. Drag and Drop the following on the design surface.

ie3

Step 4: Right click on the connection manager area and create a new OLEDB connection. We will create a connection for the AdventureWorks2012 database and the Production.Photo table.

 

IE4 IE5

 

Step 5. Use the following command to select the data from the table

declare @Ipath varchar(100)= 'C:\document\'
SELECT [ThumbNailPhoto],
@Ipath+[ThumbnailPhotoFileName] AS Imagepath
FROM [AdventureWorks2012].[Production].[ProductPhoto]

 

IE6

 

Step 6. Double click on the export column transformation editor and select the following values

IE7

 

Step 7. Build the package and execute. You should find all the images in the selected folder

IE9

 

SQL Server 2012 – How to change the collation of SQL Server.

In this article we will see how we can change the collation of a SQL Server post installation. To achieve this we need to rebuild the master database. While rebuilding the master database, the process gives an option to change the collation. Let us understand the process step by step and change the collation of an already installed SQL Server.

Step 1: Take a backup of all your system databases and user databases.

Step 2: Script out all Logins.

In this example we will change the collation fromSQL_Latin1_General_CP1_CI_AI’ to ‘SQL_Latin1_General_CP1_CI_AS’
collate1

 

Step 3: Stop the SQL Server Service.

collate3

 

Step 4: Via Command prompt locate to the Binn directory of the SQL Server

collate2

Step 5: Run the below command

sqlservr -m -T4022 -T3659  -s”INST2012_1″  -q”SQL_Latin1_General_CP1_CI_AS”

Parameters:
[-m] Single User Mode
[-T] Trace flag turned on at startup
[-s] SQL Server Instance Name
[-q] New Collation

collate5

Step 6: Restart SQL Server and check the collation

collate6

How to Rebuild the MSDB database

I was preparing a migration plan to migrate all SQL Server databases from our existing SAN to new SAN. While doing so I thought of actually doing a small practice on my laptop SQL Server. The user databases moved from the existing location to another location very smoothly. I was very happy. Now was the turn of the system databases. I started with the MSDB database. As recommended in the Microsoft technet article of relocating System DB’s, I was following the steps prescribed. Pretty simple steps. After making the file location change in the master database, I stopped the SQL service, physically moved the files to the new location and then restarted the SQL service. The MSDB database would not come up….In recovery…..

What do I do?…I didn’t even have a backup of the MSDB database. I read few tech net articles that helped me rebuild the MSDB database from scratch. Here is what needs to be done

1) Stop all SQL server services.
msdb12) Start SQL Server from the command prompt using the trace flag T3608. The exact command for this would be : NET START [Name of the SQL service]/T3608. This is what the trace flag does as per Technet.

Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require tempdb are initiated, then model is recovered and tempdb is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work.

msdb23) Now we need to detach the MSDB database from the instance. This is what needs to be done for the same. We need to run a SQLCMD command to do the same.
SQLCMD -E -S<servername> -dmaster -Q”EXEC sp_detach_db msdb”
msdb4After running this rename the existing MSDB files. Stop and restart SQL Service via the configuration manager.
msdb54) We need to locate the following .SQL file in the install folder of the SQL Server installation files.
msdb65) Open the script in SSMS and execute.

SQL Server agent was running successfully. The MSDB database was online again. The first thing I did was take a back up now.

 

 

 

SQL Server Integration Services – Foreach Loop Container

In my previous article I wrote about the For loop Container. In this article I will show how we can implement the Foreach loop container with a demo. The Foreach loop container is the third container amongst the three containers in SSIS.

The Foreach loop container can be used to loop through a set of enumerated objects like files in a folder or rows in a table. We can add different control flow tasks within the container for each enumerator or a DataFlow task to process data related to each item. Let us now implement this container with a small demo.

Implementing Foreach Loop Container

Objective: To copy files from one folder to another folder.

Step 1: We will be using the package we created in my article. Drag and drop a Foreach loop container in the design UI surface -> Drag and drop a File system task inside the container.
IS48Step 2 : Create a variable as shown below with the depicted data type and value
IS49Step 3: create 2 folders named ‘Source’ and ‘destination’ respectively. In the source folder create two files named file1.txt and file2.txt
IS50 IS51Step 4: Right click -> Choose Edit on the Foreach loop container and fill up the following as depicted below. Click on OK.
IS52 IS53Step 5: Right Click on the File System task -> On DestinationConnection -> Choose the usage type as ‘Existing Folder’ -> Choose the path of the destination folder -> click on ok.
IS54Step 6: Set the rest of the fields as below -> Click on OK.
IS55Now we are ready to test out solution. Press F5 and the 2 files should be in the destination folder.
IS56 IS57Bingo!!!! The files has been copied to the destination folder. I hope this article was useful in understanding the Foreach loop and its understanding.

SQL Server Integration Services – For Loop Container

In my previous article we discussed about the Sequence container. In this article we will implement the For loop container. The objective of this container is that it will iterate through all the tasks inside until the condition is satisfied. For every  iteration the For Loop container evaluates the condition that is specified and repeats the process until the condition stands false.

The For loop container has 3 expressions that determines the number of executions that container would undergo. let us understand these expressions briefly.

InitExpression:

This is an optional expression in the For loop container. It is evaluated before any process is started inside the loop. The major use of this expression is to set an intial value as the name indicates.

EvalExpression:

The second expression when the loop starts. It is also evaluated before any process is executed inside the container, and then again evaluated at the beginning of each loop. This determines if the loop continues or ends.
If the condition stands TRUE, the process is executed again. If the condition stands FALSE, the loop ends.

AssignExpression:

The is the 3rd condition used in the For Loop. It is used to change the value of the variable used in the EvalExpression. This expression is evaluated for each pass through the loop as well, but at the end of the workflow. This condition is optional.

Implementation of the for loop Container

Lets implement the For loop container. I will be using the project I created in the my article.

Step 1: Open the project in BIDS -> Add a Execute SQL task on the UI design surface. Insert the following script in the task

CREATE TABLE Forloop
( id INT IDENTITY(1,1) NOT NULL,
Mydate DATETIME
)

Step 2: Drag and drop a For loop container inside the design UI surface -> Drag and drop a Execute SQL Task inside the For Loop Container -> Insert the following code inside the Execute SQL Task

INSERT INTO Forloop VALUES (GETDATE())

Step 3: Click on the variable tab -> create a variable called counter with data type as Int64 or Int32.
IS44Step 4: Right Click inside the For loop container and click on edit. Fill in the expressions as depicted in the pic below -> Click on OK.
IS45Step 5: Press F5 and make sure the execution succeeds
IS46
What we observe in the database is that the table has been created with 10 rows inserted in the table.
IS47
In this is article I have tried to implement the For Loop container in a very simplified manner. I hope I was able to do that.

 

 

 

 

 

SQL Server Integration Services – Sequence Container

In this article we will understand how we can use a Sequence Container with a demo. In my previous article I wrote about Control Flow Objects in SSIS.

Sequence Container and its benefits

Sequence container groups related tasks in a package and helps in understanding the complexity of a package in a simpler way. The objective of the Sequence container is to have multiple separate control flows grouped together in the package.

Some of the benefits of the package are as below:
1) Helps in debugging.
2) If a package has multiple tasks then it is easier to group the tasks in Sequence Containers and we can collapse and expand this container for usability.
3) We can set the property of entire container rather than setting the property of individual tasks.
4) We can set the flexibility of completion of the process in the container to a transaction level i.e in case of an error the entire process can be rolled back or partial success can be achieved.

Implementing Sequence Container

Now we will implement the sequence container with a demo. In the first example we will not use TransactionOption but in the second example we will. Let us begin. We will be using the project we created in my article.

Step 1: Open the SSIS Project -> Click on the package MyPackage.dtsx -> Drag and drop a Sequence Container from the toolbox on the design surface -> Drag three Execute SQL Task objects in the Sequence ContainerIS32Step 2: Rename the 1st Task as Create Table. Rename the second task as Insert Values. Rename the 3rd Task as Update Values -> Drag the constraint from the 1st task onto the Insert Values Task and then drag the constraint from the Insert Values Task onto the Update Values Task. The structure should look like below
IS33Step 3: Right click on the Create Table Task -> Click Edit -> Change the connection to AdventureWorks2008 -> Click on the SQLStatement and insert the following code -> Click on OK.

CREATE TABLE [SequenceContain]
(
id INT NOT NULL,
NAME VARCHAR(20),
age INT
)

Step 4: Implement the same for Insert Values Task apart from the SQL. Insert the below SQL code

INSERT INTO dbo.SequenceContain VALUES
(1,'Amit A',34),(2,'Amit B',34),
(3,'Amit C',24),(4,'Amit D',44),
(5,'Amit E',64),(6,'Amit F',34)

IS34Step 5: Implement the same for Update Values Task apart from the SQL. Insert the below SQL code

UPDATE SequenceContain
SET name = 'Bodhi'
WHERE id = 3

IS35Step 5: Press F5  and the package execution begins. The entire package will turn green when the execution succeeds
IS36Step 6: Lets verify via SQL Server if the table and data has been created and modified.
IS37

TransactionOption

Example 1:

Now lets tweak the third task i.e Update Values task and enforce an error. We will change the TransactionOption to NotSupported.

Step 1: Right click on the Update Task -> Goto SQLStatement -> insert the following code below

UPDATE SequenceContai
SET name = 'Bodhi'
WHERE id = 3

We have kept the name of the table incorrect to enforce the error.

Step 2: Select the Sequence Container -> On the properties tab -> TransactionOption -> Select Not Supported.
IS38Step 3: Not lets drop the table from the database so that the create table does not error out.
IS39Step 4: Press F5 and run the package and lets observe what happens
IS40

Observation: What we observe is that the package has failed(red) and the update values Task has also failed(red). Let now have a look at the database and see what impact has this package made.
IS41What we observe is that create table and insert values task has succeeded but the update values task has failed. So this what NotSupported does. It does not do a full roll back.

Example 2:

Now we will change the TransactionOption to Required and observe what happens
Step 1 : Drop the table from the database
Step 2 : Change the TransactionOption to Required
IS42Step 3: Press F5.
IS40
Observation:

The Package fails but lets verify what has happened at the database level by running a select on the table.
IS43
The entire process has been rolled back. The table structure does not exist even though the create table had succeeded. So this is what The Required option does. In case of an error it rolls back the entire package.

In this article I have tried to explain the use of the sequence container in a simplified manner. I hope i was able to do that.

 

 

SQL Server Integration Services – Control Flow Objects

In my previous article on SSIS we spoke about Packages,Connections and Data Sources. In this article we will talk about Control Flow Objects and how we can use them.

So what is Control Flow?

Control Flow is the engine which manages the workflow of the tasks created coupled with the control flow containers and constraints. SSIS provides a Design UI which displays a workspace where we can configure different control flow objects.

SSIS provides three types of control flow objects
1) Tasks – Tasks are objects that perform specific work
IS16
2) Containers – Containers help group different tasks
IS17
3) Constraints – Constraints help connecting different tasks and containers. Its also helps to define the order of execution of different tasks.
IS18

A package must contain at least one task that performs a certain operation. When multiple tasks are configured, then a container can be used to group them together.
IS19

 

Container

SSIS provides three types of Containers. Here is a brief description about the three Containers. We will deep dive into the Containers later.

1) Sequence Container : This allows us to group multiple tasks together. The benefits of this feature is that we can control the entire group rather that managing each task separately. It also means we can run different tasks sequentially or parallel. The entire group can collapsed or expanded. The Sequence container is the 3rd container in the toolbox
IS202) For Loop Container : This provides the same functionality as the sequence container but also lets you run tasks multiple times based on the condition provided. The For Loop is the 1st tool in the tool box
IS213) Foreach Loop Container : This allows looping but instead of a condition the looping happens over objects such as files and folders or tables in a database.
IS22

Implementation of Control Flow Task

We will now implement the above by creating and editing a control flow task and executing the package within the SSIS environment. We will be using the same project we created in our previous article for this demo.

Step 1: Open the Project using BIDS -> Select Mypackage.dtsx -> Click on the Toolbox -> Drag and Drop the Execute SQL Task on the Designer Surface. Rename the task as Update Table
IS23 IS24
Step 2: Right Click on the Task – > Click on Edit -> Change the Connection to AdventureWorks -> Click on the SQLStatement and insert the below query – > Click on OK.
IS25 IS26 IS27Query:

UPDATE Production.ScrapReason SET
ModifiedDate = '2014-06-01 00:00:00.000'
WHERE name = 'Drill pattern incorrect'

Step 3: click Start Debugging on the Standard toolbar -> If the execution is correct then the task would be green.
IS28 IS29Step 4: Click on the Progress tab to view the execution details of the task. -> Stop the execution by clciking on the stop debugging button – > Save the project.
IS30 IS31

The objective of this article was an introduction to the concept of Control Flow and Tasks with a small demo. I hope I was able to do that. In my next article I will write about Container with a broader perspective.

 

 

SQL Server Integration Services – Packages, Datasources and Connections

In this article we will see how we can create a SSIS project via visual studio and then create a package and linking the packages to data sources along with connections. For this demo i will be using SQL Server 2008. The steps remain same for SQL Server 2008 R2 and SQL Server 2012.

creating a SSIS project…

Step 1: Open SQL Server Business Intelligence Development Studio(BIDS). Go to File -> New -> Project
IS1

 

 

 

 

 

 

 

Step 2 : ON the new Project dialog box choose Integration Services Project. Give the Project a name and location as shown below. Click on OK. The  Project will appear on the Solution explorer. By default a package is already created named Package.dtsx
IS2IS3

Step 3: let us rename this package as MyPackage1. Right click on the Package and select rename. Change the name to MyPackage.dtsx. Click Yes on the confirmation dialog box.
IS4IS5
Step 4: We will create two more packages named DC.dtsx and DP.dtsx. Right click on SSIS package on solution explorer and click on New SSIS Package. A package would be automatically added with default name Package(n).dtsx. We will rename the package as DC.dtsx. Repeat the same steps and create another package named DP.dtsx. Click on save all button to save all the changes made.
IS6 IS7So this is how we will create a SSIS project with packages. In the next section we will create data sources for the project.

 creating data sources..

Step 1: Open the Project in BIDS. Right click on Data Sources and select New Data Sources. On the Data source wizard click on new and select a server name and database name. In this case we will choose AdventureWorks as our database.
IS8 IS9 IS10Step 2: Click on OK. Click Next and click on Finish. The new data source will now appear under Data Sources
IS11 IS12 In the next section we will create new package connections from the Data Sources we created.

Creating Connections

Step1 : Click any one of the packages. Right Click on the connection managers window and select New Connection from Data Source. Select the Data source we created name AdventureWorks. Click on OK.
IS13 IS14The new connection connection will now appear in the connection managers window for the package. We need to create the same for the rest of the two packages.
IS15

 

%d bloggers like this: