CODEWITHSUNDEEP
excelssis
Learner
Learner

Reputation: 3

SSIS Automate Excel File Generation From Database

I have been facing issues while package execution.

What I have Done :

I have created a Template which has only header in it and I have a table "Product". I tried to insert the data into excel sheet from "Product" table by using the excel template. I have used 3 variable.

Flow : File System Task => Data Flow Task => OLE DB Source => Excel Destination.

Error :

[Excel Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

[Excel Destination [2]] Error: Opening a rowset for "result$" failed. Check that the object exists in the database.

[SSIS.Pipeline] Error: "Excel Destination" failed validation and returned validation status "VS_ISBROKEN".

Thanks

Upvotes: 0

Views: 930

Answers (1)

Marko Ivkovic
Marko Ivkovic

Reputation: 1290

This is miracle. I just solved this error...

You don't need to create a template (dummy) excel, you need only 3 components.

1. Create Execute SQL Task according to this tutorial.

When set everything except SQLStatement I suggest creating empty excel and connect to OLE DB source (your server table). When you add excel destination and connect with excel destination manager, you will see question that VS ask if you want to create table like this query (but with different data types):

CREATE TABLE `Result` (
    `ColName1` NVARCHAR(255),
    `ColName2` NVARCHAR(255),
    `ColName3` NVARCHAR(255),
    `ColName4` NVARCHAR(255)
)

I don't know if I explained well, but you have previous step in tutorial.

2. Execute this task

3. Add Data Flow Task with OLE DB Source and Excel Destination

With OLE DB select all that you need from your Product table

Connect Excel Destination with SAME Excel Connection Manager like in Execute SQL Task. From drop-down select Result sheet name.

When you finish this delete this empty excel

4. Create a variable that will contain path and excel file name

5. According to tutorial from first step, click on Excel Connection Manager -> Properties -> Expressions -> ... (on right) -> ExcelFilePath (from drop-down). In 2nd column write @[User::YourVariableName]

Crucial step

6. In control flow, click on Data Flow Task and in properties for DelayValidation set True.



If you have a question, write me in comment below.

Upvotes: 2

Related Questions