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

Comments are closed.

Powered by Netfirms