SSIS: The process cannot access the file because it is being used by another process.

Standard

AAAAAAAAAAAGGGGGGGGGGGHHHHHHHHHHHHHHHHHHHHHH!!!!!!

I’ve just spent a frustrating few minutes trying to figure out why my SSIS package fails to move an Excel file I’m using as a source to an archive location.

The package is pretty simple:

ImportFromSpreadsheet

All it does is receive a folder location of x number of Excel files, loops over them and reads the data, then the Archive the imported files File Task moves them to an archive folder. I was encountering the infuriating error:

SSIS: The process cannot access the file because it is being used by another process.

The problem was that I had set the Retain Same Connection property to true for the SourceConnectionExcel connection manager.

RetainConnection

This means that SSIS will lock this file until the package stops executing. In turn meaning you won’t be able to move the file in File Task operation. Once I’d altered the setting, my package worked without issue.

Feelsgoodman.jpg

 

Advertisements

Rows to string – 2 ways..

Standard

Alrighty then, new post time, more so to keep this in a place I can find it because there are a number of places that you can find how to do this, but…

A work colleague and good friend was asking for ways to convert rows to a string, there are at very least two ways to do this:

1: Using FOR XML PATH

DECLARE @Tbl TABLE
(
Name VARCHAR(20)
);

INSERT INTO @Tbl VALUES
('Jim'),
(
'Tim'),
(
'Kim');

DECLARE @ReturnVar VARCHAR(256);

SELECT STUFF((SELECT ',' + Name
                          
FROM @Tbl
                          
FOR XML PATH('')),1,1,'') AS Name;

 

2: Using COALESCE

DECLARE @Tbl TABLE
(
Name VARCHAR(20)
);

INSERT INTO @Tbl VALUES
('Jim'),
(
'Tim'),
(
'Kim');

DECLARE @ReturnVar VARCHAR(256);

SELECT @ReturnVar = COALESCE(@ReturnVar + ', ', '') + Name
FROM @Tbl;

SELECT @ReturnVar;

Pretty simple huh?