Requirement & Preface
In this article, we're going to output data from database to multiple flat files.
We are going to learn:
1. How to use a variables to save a query result set from database.
2. How to get each value from this result set.
3. How to use Loop Container to loop a result set from database.
4. How to configure the connection string for a flat file by using variable.
5. How to use ADO.NET and OLE DB connection
6. How to use variable when do query in OLE DB source
Testing data and table are from the first article SSIS Step by Step 001 - Load data from a flat file
Here're some records from this table.
AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode SpatialLocation
25 9178 Jumping St. NULL Dallas 73 75201 NULL
26 5725 Glaze Drive NULL San Francisco 9 94109 NULL
27 2487 Riverside Drive NULL Nevada 74 84407 NULL
28 9228 Via Del Sol NULL Phoenix 6 85004 NULL
29 8291 Crossbow Way NULL Memphis 72 38103 NULL
30 9707 Coldwater Drive NULL Orlando 15 32804 NULL
31 9100 Sheppard Avenue North NULL Ottawa 57 K4B 1T7 NULL
32 26910 Indela Road NULL Montreal 63 H1Y 2H5 NULL
We want to extract data by City and output these data to different (city) files.
Steps
1. Create a new package - CH02_OutputFlatFile
Create two variables:
The first one is Citys, Data Type - Object, we will query all cities from database and save to this variables.
The second one is City, Data Type - String, the single city from Citys variable.
Drag an Execute SQL Task to control flow pane, name - EST_LoadCitys
2. Edit EST_LoadCitys
Notice the connection type is ADO.NET, we will load all data save as a FULL RESULT SET to a variable.
SQL Statement will extract and group by cities from database.
SELECT City
FROM dbo.[Address]
GROUP BY City
3. See Result Set pane, that means all values from database query will save into user variable Citys.
4.Drag a Foreach Loop Container and connect to EST_LoadCitys, click Foreach Loop Container and edit it.
5. Edit Foreach Loop Editor
We choose Foreach ADO Enumerator as an Enumerator.
For ADO object source variable, user variable Citys is the source in loop container.
Mark - Rows in the first table.
6. In Variable Mapping, set City variable, which means in every loop, the value of City gets from each row of Citys variable in step 5.
7. Drag a Data Flow Task, add it into Loop Container.
Then, go into DFT_GenerateFiles data flow task
8. Add an OLE DB Source
9. Edit the OLE DB Source
Add an OLE DB connection manager, then we use SQL to load data, notice there's a parameter ?
SELECT *
FROM dbo.[Address]
WHERE City = ?
In every loop, string variable city will get a value from object variable Citys, we use the variable city to query the address information.
Click 'Parameters..' Set user variable city as a parameter in above SQL.
Click OK.
10. Do mapping with this query, we don't need Address ID and Spatial Location as usual. The we finished the OLE DB Source task.
11. Drag a flat file destination into data flow pane, name it as 'FFD_AddressByCity', and pass the connection to FFD_AddressByCity.
12. Edit FFD_AddressByCity and new a connection manager.
Choose 'Delimited'
13. Input information as red cycle marks.
Note - the file name ****\CH02-Citys.txt. This file doesn't exist, I click browse to create one. But actually all data won't go into this file, we will configure the file path later. Because there're many cities in database, each city have a flat file which have the current city address information.
Click OK.
14. When the connection manager done, do mapping for flat file.
The input is from OLE DB SQL Query result set.
The destination is the flat file.
15. Back to the data flow pane.
Click CM_FF_AddressByCity in Connection Manager Pane, we're going to configure the file path.
Connection String - the destination file path
Expression - Use variable to configure some properties of connection manager (CM_FF_AddressByCity)
Click 'Expression' in properties of 'CM_FF_AddressByCity'
Click '...' to configure ConnectionString property of connection manager.
16. Expression Builder
My current outgoing folder is located in Disc F. You can modify it to your real local path
Notice the file name will be : CH02-Citys-[City Name].txt
"F:\\SSIS Projects\\Testing Files\\CH02-Citys-"+ @[User::City] +".txt"
Click Evaluate Expression to see if the expression is correct.
Click OK.
17. Execute package, see whole green - PASS !
18. Go to output folder seeing many output files there, the file names are what we want.
19. Validate the information.
Address are divided by city name.
Please let me know if you have any questions about this blog.
Contact to me by email - simonlvpin@yahoo.com
Or Skype - simonlvpin
