Rename a file using SSIS 2008

No Comments

Written by: Luis Miguel – Originally Published on: August 17, 2013

In my previous post I explained how to unzip a file using SSIS. The scenario presented unzipped a single CSV file and the file name was static, so if you executed the package more than once the CSV file would be overwritten.

In today’s post I’m going to present a method to rename the unzipped file so you can preserve the data when the package is executed multiple times.

Steps used to implement this solution

1 – Create a string variable to hold a daily timestamp

The complete package with the new_name variable

The complete package with the new_name variable

2 – Add an Execute SQL Task to populate the variable

Populate the new_name variable

Populate the new_name variable


Enter this T-SQL script to assign a value to the new_file variable
DECLARE @import_timestamp CHAR(8)
DECLARE @new_file VARCHAR(64)
SET @import_timestamp = CONVERT(VARCHAR, GETDATE(), 112)
SET @new_file = ‘c:\unzip\sale_orders_’ + @import_timestamp + ‘.csv’
SELECT @new_file

3 – Add a Script Task component and enter the C# code used to rename the file
The "rename csv file" Script Task

Enter this C Sharp code to rename the file
public void Main()
{
string old_file;
string new_file;
old_file = “c:\\unzip\\sale_orders.csv”;
new_file = Dts.Variables["User::new_name"].Value.ToString();
File.Move(old_file, new_file);
}

Unzipping a file with SSIS 2008

No Comments

Written by: Luis Miguel – Originally Published on: August 11, 2013

The Business Intelligence Development Studio set of tools does not include the ability to unzip a compressed file natively.

If your project requires this functionality you will have resource to third party tools or some other technique to achieve the objective.

In this post I’m going to show how to unzip a compressed file using the JAR program which is part of the Java Developers Kit.

Here is the detailed list of steps I used to perform this task
1 – Download and install the Java Developers Kit
2 – Create a batch file that sets the path to the Bin folder and executes the JAR program
3 – Build an SSIS 2008 package that calls the batch file

Step number 1, downloading and installing the Java Developers Kit should be a very simple and smooth process, I have done this in several servers without any issues at all.

For step number 2, use Notepad to create a file with the extension CMD with the following code
SETLOCAL
SET PATH=”C:\Program Files\Java\jdk1.7.0_25\bin”
CD c:\data\orders
jar xf sale_orders.zip

For the string used in the SET PATH command just look up where in your system you installed Java

In this case I’m setting the working directory to c:\data\orders because that is where I have placed the zipped file, the batch file, and that is where the unzipped file will be placed

The working directory

The working directory

In step number 3 we are going to use an Execute Process Task to call the batch file from the SSIS package

The Execute Process Task

The Execute Process Task


The unzipped CSV file

The unzipped CSV file

The normal behavior of the JAR program is overwrite the destination file, so if you execute the batch file more than once and the target (unzipped file) already exists it will be overwritten. This could be the desired behavior and in a way is a good thing because that means you don’t have to worry about the program failing because the uncompressed file exists.

But it is also possible that you don’t want to lose the data (CSV files) from previous runs, to achieve this it will be necessary to rename the CSV file before the package is executed again, in my next posts I’m going build a package that does that.

Powered by Netfirms