CODEWITHSUNDEEP
sql-serverexcelssis
Eric Ness
Eric Ness

Reputation: 10417

How to split table data into separate named Excel files using an SSIS package?

I'm working with a set of data from SQL Server that I'd like to get into a group of Excel files. This task needs to be automated to run on a monthly basis. The data looks like

Site    ID      FirstName   LastName
------  ------- ---------   ---------
North   111     Jim         Smith
North   112     Tim         Johnson
North   113     Sachin      Tedulkar
South   201     Horatio     Alger
South   205     Jimi        Hendrix
South   215     Bugs        Bunny

I'd like the results to look like

In Excel file named North.xls

ID      FirstName   LastName
111     Jim         Smith
112     Tim         Johnson
113     Sachin      Tedulkar

In Excel file named South.xls

ID      FirstName   LastName
201     Horatio     Alger
205     Jimi        Hendrix
215     Bugs        Bunny

There are between 70 and 100 values in the Site column that I'd like to split upon. I'm using SSIS to perform this task, but I'm getting stuck after I've pulled the data from SQL Server with a OLE DB Source task. What should come next? If there is an easier way to do this using other tools I'm open to that too.

Upvotes: 2

Views: 12430

Answers (4)

harry
harry

Reputation: 11

I found the solution the problem was with the excel destination.I reinstalled Microsoft access engine Redistributable(32-bit version). All is working good. I had a 64bit and 32 bit problem they where not compatibility as my system was 64 bit.

Upvotes: 1

user756519
user756519

Reputation:

You have probably found an answer to your question by now. This is for other users who might stumble upon this question. Following example shows how this can be achieved dynamically for any number of Sites that might be present. The example was created using SSIS 2008 R2 with SQL Server 2008 R2 database.

Step-by-step process:

  1. Execute the scripts given under SQL Scripts section in an SQL Server database to create a table named dbo.Source and populate with data (similar to data given in the question). It also creates a stored procedure named dbo.GetSiteData.

  2. On the SSIS package's connection, create an OLE DB connection to connect to the SQL Server. We will get to Excel connect later in the steps.

  3. On the SSIS package, create 6 variables as shown in screenshot #1. Populate the variable Site with value Template, this is a sample value that will be used to evaluate other expressions. Set the variable SQLUniqueSites with value SELECT DISTINCT Site FROM dbo.SourceData. Set the variable ExcelFolder with value C:\temp\

  4. Select the variable ExcelFilePath and press F4 to view Properties. Change the property EvaluateAsExpression to True and set the property Expression with value @[User::ExcelFolder] + @[User::Site] + ".xls". Refer screenshot #2.

  5. Select the variable ExcelSheet and press F4 to view Properties. Change the property EvaluateAsExpression to True and set the property Expression with value given under ExcelSheet Variable Value. Refer screenshot #3.

  6. On the SSIS package's Control Flow tab, place an Execute SQL Task and configure it as shown in screenshots #4 and #5. This task will fetch the unique Site names.

  7. On the SSIS package's Control Flow tab, place a Foreach Loop container after the Execute SQL Task and configure the Foreach Loop container as shown in screenshots #6 and #7 This loop will loop through the result set and will read each site into a variable. This variable is then used to provide the name for Excel file as well as the parameter to the stored procedure in a Data Flow Task that will be added shortly.

  8. Inside the Foreach Loop container, place an Execute SQL Task and then place a Data Flow Task. At this point, the Control Flow tab should look like as shown in screenshot #8. Your package might show errors at this point, we will fix that soon in the next steps. We will configure the Data Flow Task and then will get back to Execute SQL Task within the Foreach Loop container.

  9. Inside the Data Flow Task, place an OLE DB Source and configure it as shown in screenshots #9 and #11. This will fetch the data from table based on a given site. Click on the Parameters... button to set the query parameters.

  10. If the table field datatypes are in VARCHAR, then we need to convert it into NVARCHAR (unicode format), otherwise this step is not needed. In the Data Flow task, place a Data Conversion transformation after the OLE DB Source and configure it as shown in screenshot #12.

  11. Next, inside the Data Flow Task, place an Excel Destination, click on the first New... button as shown in screenshot #13.

  12. On the Excel Connection Manager dialog, provide an Excel File Path and click OK. Refer screenshot #14. Back in the Excel Destination, click on the second New... button as shown in screenshot #15. On the Create Table dialog, make sure the script is as shown in screenshot #16 and click OK. When displayed with a warning as shown in screenshot #17, click OK. Select the value Template from the Name of the Excel sheet dropdown as shown in screenshot #18. Configure the columns as shown in screenshot #19.

  13. On the SSIS package's connection manager, select the newly created Excel connection manager and press F4 to view the properties. Change the Name property value to Excel. Change DelayValidation to True so that if the file Template.xls doesn't exist, you don't get an error message. Set the ServerName Expression with value @[USer::ExcelFilePath]. Refer screenshot #20. NOTE: An Excel file should have been created in the path C:\temp\Template.xls. You might want to save it so that you don't encounter during future design changes. You can still recreate it if the file is deleted.

  14. Go back to the Excel Destination and configure it as shown in screenshot #21. Once the Data Flow Task is configured, it should look like as shown in screenshot #22.

  15. Back in Control Flow tab, configure the Execute SQL Task within the Foreach Loop container as shown in screenshot #23. This task will create new Excel spreadsheets for each Site name.

  16. Screenshot #24 shows contents in folder c:\temp\ before the package execution.

  17. Screenshots #25 and #26 show the package execution.

  18. Screenshot #27 shows contents in folder c:\temp\ after the package execution.

  19. Screenshots #28 and #29 show the contents of the newly created Excel spreadsheets North.xls and South.xls. Both the sheets contain the data respective the sites of the same name.

Hope that helps.

ExcelSheet Variable Value:

CREATE TABLE `Template` (`Id` Long, `FirstName` LongText, `LastName` LongText)

SQL Scripts:

CREATE TABLE [dbo].[SourceData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Site] [varchar](50) NOT NULL,
    [FirstName] [varchar](40) NOT NULL,
    [LastName] [varchar](40) NOT NULL,
CONSTRAINT [PK_SourceData] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.SourceData (Site, FirstName, LastName) VALUES
    ('North', 'Jim', 'Smith'),
    ('North', 'Tim', 'Johnson'),
    ('North', 'Sachin', 'Tendulkar'),
    ('South', 'Horatio', 'Alger'),
    ('South', 'Jimi', 'Hendrix'),
    ('South', 'Bugs', 'Bunny');
GO

CREATE PROCEDURE dbo.GetSiteData
(
    @Site   VARCHAR(50)
)
AS
BEGIN   
    SET NOCOUNT ON;

    SELECT  Id 
        ,   FirstName
        ,   LastName
    FROM    dbo.SourceData
    WHERE   Site = @Site
END 
GO

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Screenshot #7:

7

Screenshot #8:

8

Screenshot #9:

9

Screenshot #10:

10

Screenshot #11:

11

Screenshot #12:

12

Screenshot #13:

13

Screenshot #14:

14

Screenshot #15:

15

Screenshot #16:

16

Screenshot #17:

17

Screenshot #18:

18

Screenshot #19:

19

Screenshot #20:

20

Screenshot #21:

21

Screenshot #22:

22

Screenshot #23:

23

Screenshot #24:

24

Screenshot #25:

25

Screenshot #26:

26

Screenshot #27:

27

Screenshot #28:

28

Screenshot #29:

29

Upvotes: 15

Sam
Sam

Reputation: 7678

Not sure if this would work, but put all your condition queries in a table along with another field for a file name. Then use a for loop to go through each of those and dynamically modify the select clause of the transform task. SSIS calls these dynamic modifications - expressions.

You do a select on your table of statements/conditions and then throw that into an object variable. The object variable is then used in the for loop.

The only thing I'm not sure of is the mapping into the excel file name.

edit: Also found this, which uses a linked server http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx

Upvotes: 1

K Richard
K Richard

Reputation: 1984

I think SSIS is a good tool to use, and you have a couple of options.

Basically, you can use the multicast and conditional split objects to achieve what you want.

Here is how I would do it:

1) I would create the two MS Excel files with all of the data in them as I would like to see it. Delete the data and keep these as template files and make a copy for the full files.

2) Setup file connections to these files in the connection manager.

3) Make a file system task that overwrites the full files with the template at the start of the job (other ways to do this, but I like this one the best).

4) Add data flow task and in it drop in an OLE DB Source, a Multicast, two Conditional Splits, and two MS Excel Destinations.

5) Configure each of these and you should be done. The configuration is pretty intuitive and in the order I have above.

Upvotes: 1

Related Questions