Thursday, May 31, 2012

SSIS Step by Step 002 - Output data to multiple flat files

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. 



Get all SSIS Step by Step, refer to  SIMON'S SSIS Step by Step

Please let me know if you have any questions about this blog.
Contact to me by email - simonlvpin@yahoo.com
Or Skype - simonlvpin






10 comments:

  1. Great and easy to understand example. Just what I've been looking for.

    ReplyDelete
  2. This was very very very helpful! Thank you!

    ReplyDelete
  3. Thanks so much for this article! it was very helpfull..

    ReplyDelete
  4. h/t for this. good stuff.

    ReplyDelete
  5. Thanks a billion Simon. Your article save my bacon

    ReplyDelete
  6. Excellent article but I am getting following message at the execute SQL task.
    I have double checked my connection and SQL both are working ok. Please can you help.

    [Execute SQL Task] Error: Executing the query "SELECT Src
    FROM [myDB].[dbo].[myTable..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::ResultSet" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.
    ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Cheers,

    ReplyDelete
  7. Really excellent. Thanks for writing up such detailed instructions.

    ReplyDelete
  8. Big thumbs up, package worked PERFECTLY the first time I ran it, even though I was using similar but different data. That never happens.

    ReplyDelete