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.

Exporting a dataset to a Flat File Destination in SSIS 2008 [DRAFT]

No Comments

Written by Luis Miguel – Originally Published on Feb. 6, 2012

Here is a fairly detailed description of the request coming from management:

1 – Gather a list of new records from the Customers table and save it to a flat file on a local disk nightly
2 – Generate a fixed width ASCI file with the following structure:
Attribute (Field length)
- Customer Number (16)
- Customer Name (128)
- Address1 (128)
- Address2 (128)
- City (64)
- State (2)
- Zip (10)
- Company Id (16)

3 – Fill shorter values with spaces so all columns stay aligned in the flat file
4 – Segregate the datasets by accounting company and put them in separate text files
5 – Generate a file name with the following scheme companyname_yyyymmdd.txt

The following is a step-by-step guide of the process implemented using Integration Services 2008 to fulfill the requirements

I – Create a table with CHAR data type columns

There are two tables involved in this process
a – CUSTOMER_EXPORT – Is the list of companies to process since management does not want to include all companies in this process, the stored procedure reads from this table and performs the extract/save for the records marked export_ = 1 (True)

The Customer Export table where accounting company databases are flagged for processing

b – CUSTOMERS is the complete list of customers from all the companies, every single column on every single row in this table needs to have a value (a space for empty fields, empty fields cannot be NULL) so that in the query the empty fields become a string of spaces

The structure of the Customer table

The Customer table in design view, all relevant fields are CHAR type and do not allow nulls

The Customer table data

The Customer table data, the field 'updated_on' is used to identify (new) records to be exported

2 – Build a package to extract the data and save it to disk
3 – Write a stored procedure to drive the SSIS package
4 – Add an SQL Server job that executes the stored procedure every night

Create a new SSIS package and set its security to “Encrypt Sensitive with password”

2 – Create 3 package variables and set their data type to STRING
a – flat_file, company_id, export_date

The three package variables that will be passed as arguments when executing the package from the stored procedure


3 – Connection Managers
a – Add an OLE DB connection to the source database, use SQL Server authentication, in the real world your Windows credentials will not be calling the package and reading the data from the database

The OLE DB connection using SQL Server authentication and the option to save the password, passwords are encrypted everywhere they are used.


b – Add a flat file connection to the target text file, use an expression so that the destination file name can be dynamically generated, in this case the expression is “ConnectionString = @[User::flat_file]

The flat file connection using an expression for the connection string so the destination file name can be generated dynamically


4 – Add a Data Flow Task
a – Add an OLE DB source to the Customer table and use the SQL Command

SELECT
[customer_number] +
[name] +
[address1] +
[address2] +
[city] +
[state] +
[zip] +
[company_id] AS customer_record
FROM customers WHERE company_id = ? AND updated_on = ?

b – Set the input parameters to the variables company_id and export_date

[insert picture here]

c – Add a Flat File Destination and connect the output of the OLE DB data source to it

[insert picture here]

5 – Create a stored procedure to call (execute) or “run” the package
a – Make sure you encrypt the store procedure so that the package password is not visible
b – Keep the package password in a safe place, you will need it to open the package in BIDS

CREATE PROCEDURE [dbo].[usp_client_file]
WITH ENCRYPTION
AS
DECLARE @year_ CHAR(4)
DECLARE @month_ CHAR(2)
DECLARE @day_ CHAR(2)
DECLARE @export_date VARCHAR(128)
DECLARE @insert_command VARCHAR(2048)
DECLARE @company VARCHAR(128)
DECLARE @company_id VARCHAR(16)
DECLARE @flat_file VARCHAR(128)
DECLARE @dtexec_ VARCHAR(2048)
SET @year_ = CAST(DATEPART(year, getdate()) AS CHAR(4))
IF DATEPART(MONTH, GETDATE()) < 10 SET @month_ = ’0′ + CAST(DATEPART(MONTH, GETDATE()) AS CHAR(2)) ELSE SET @month_ = CAST(DATEPART(MONTH, GETDATE()) AS CHAR(2))
IF DATEPART(DAY, GETDATE()) < 10 SET @day_ = ’0′ + CAST(DATEPART(DAY, GETDATE()) AS CHAR(2)) ELSE SET @day_ = CAST(DATEPART(DAY, GETDATE()) AS CHAR(2))
SET @export_date = @year_ + @month_ + @day_
DECLARE db_cursor CURSOR FOR
SELECT name FROM [dbo].[customer_export] WHERE export_ = 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @company
WHILE @@FETCH_STATUS = 0
BEGIN
SET @company_id = (SELECT company_id FROM customer_export WHERE name = @company)
SET @dtexec_ = ‘dtexec /FILE c:\ssis\packages\clients_file.dtsx /DE “y0ur_p@ssw0rd” /SET \Package.Variables[User::flat_file].Value;”\”c:\exports\’ + @company + ‘_’ + @export_date + ‘.txt”" /SET \Package.Variables[User::export_date].Value;’ + @export_date + ‘ /SET \Package.Variables[User::company_id].Value;’ + @company_id + ”
EXEC xp_cmdshell @dtexec_
FETCH NEXT FROM db_cursor INTO @company
END
CLOSE db_cursor
DEALLOCATE db_cursor

And that takes care of the assignment, if you schedule this stored procedure to run once a day, the package will extract a list of new customers and save it to local storage using a unique file name driven by a timestamp

Using the SSIS 2008 Foreach Loop Container

No Comments

Written by Luis Miguel – Originally published on November 27, 2011

This post is a demonstration of how to use the SSIS 2008 Foreach Loop Container to execute a routine against a dataset one record at a time. I have chosen the DBA task of fixing orphaned users to illustrate this technique.

The challenge: We need to restore several databases that are ready for testing from our Development environment into our UAT instance, as this databases are restored the login/user “links” are lost and we end up with orphaned users, if we restore a dozen databases and our testing group consist of another dozen users the task of manually fixing them becomes a little daunting.

The solution: Use an SSIS package to automate the process of executing the system stored procedure SP_CHANGE_USERS_LOGIN to each an every user on each and every database

Fig. 1 – A list of “fake” databases and users in my Lab’s UAT system, the target of the multiple database restores

List of databases and logins

List of databases and logins

Fig. 2 – The user ABROWN exists “Without Login” after the restore

Database user without login

Database user without login

Fig. 3 – A high level view of the package using the Foreach Loop Container

High level view of the package with the Foreach Loop Container

High level view of the package with the Foreach Loop Container

1 – Add an OLE DB connection to the package

The OLE DB connection

The OLE DB connection

2 – Add a package level variable and set its type Object, for this example I called this variable “db_login_ado”

3 – Add an Execute SQL Task (code below) and set the ResultSet property to “Full result set”
SELECT D.name AS database_, L.name AS login_
FROM sys.databases D, sys.server_principals L
WHERE D.database_id > 4 AND (L.type_desc = ‘sql_login’)
ORDER BY D.name

Execute SQL Task

Execute SQL Task

3a – Add a result set, call it 0 (zero) and assign it to the db_login_ado variable

Result set 0 of the Execute SQL Task

Result set 0 of the Execute SQL Task

4 – Add a Foreach Loop Container and set it’s collection to “foreach ado enumerator”

4a – In the Collection area of the this container set the “ADO object source variable” property to the variable coming from the Execute SQL Task

The Foreach Loop Container

The Foreach Loop Container

5 – Add two String variables, one for the databases and the other one for the logins

6 – In the Variable Mappings area of the container add two entries one for each one of the string variables you just added in the previous step. Make sure your “database” variable, which the first column of the dataset is set to Index 0 and the “logins” variable, which the second column in the dataset is set to index 1

Variable Mappins of the Foreach Loop Container

Variable Mappins of the Foreach Loop Container

7 – Add an execute sql task to the foreach container
DECLARE @dbname VARCHAR(128)
DECLARE @dbuser VARCHAR(128)
DECLARE @commandchangeuser VARCHAR(1024)
SET @dbname = ?
SET @dbuser = ?
SET @commandchangeuser = ‘USE ‘ + @dbname + ‘; IF EXISTS (SELECT name FROM sysusers WHERE name = ”’ + @dbuser + ”’) EXEC sp_change_users_login ”auto_fix”, ‘ + @dbuser + ”
EXECUTE(@commandchangeuser)

Execute SQL Task inside the Foreach Loop Container

Execute SQL Task inside the Foreach Loop Container

7a – Map the input parameters to the string variables set in the Foreach Loop Container, parameter names must be 0 and 1, the parameter size is the length of the VARCHAR data type

Parameter Mappings of the Execute SQL Task

Parameter Mappings of the Execute SQL Task

A successful run of the package

The execution of the pacakage completed successfuly

The execution of the pacakage completed successfuly

The user ABROWN is now linked to the login ABROWN after running the package

User status after the package ran

User status after the package ran

Powered by Netfirms