4

I am working on SSIS package that is fetching data from SQL server and inserting into Excel file... in Data Flow Task i've used OLE Db source and Excel destination.

After configuring OLE Db source i can preview the resulting row and even data flow task executing successfully without giving any error.. but not inserting any row..

Improve this question

7 Answers 7

Reset to default
4

Well fortunately i found the answer,I did two things..

  1. I just deleted the old Data Flow Task on which i have tried so many different settings and parameters... and Build the Data Flow Task all over again...

  2. In SQL query i'd wrote the Fully qualified name instead of 'Use Database' Statement.

Hope this will help someone..

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yeah, you should definitely just put a SQL query in there - you should be able to select the database in the properties of the data flow source.
I just ran into a similar issue. In my case, the destination component reported writing all ~30k rows, but SELECT COUNT(*) only showed ~10k rows. Re-running the package causes it to attempt to add the missing ~20k rows and report success, but the number of rows in the database doesn't change. Instead of rebuilding the whole task, I copy-pasted the contents of the existing task into a new one, and then deleted and re-added the destination. This fixed the problem.
Welcome to SSIS - the land where deleting all your work and re-doing it works and is faster than root cause analysis and finding the issue. This happens to me any time I make changes to my queries or my destination file, even after trying all of the recommended troubleshooting hints the delete/re-create is what I always end up doing to resolve this issue.
3

Have you tried writing to a flat (text) file first to be certain that data's coming out of your data flow task and going into the results?

Be absolutely sure that you've mapped the columns from the source to the target as well.

Improve this answer

1 Comment

thanks N West, i've tried the things you suggested but still no luck.. i just build DFT from scratch and few changes in Sql script and it's start working...
1

Here's a clear explanation what's going on. Most likely, you are using table variable... SSIS OLE DB Source Using Table Variables Returns No Rows

Improve this answer

Comments

0

My issue was a print statement in the SQL command. I commented it out and it worked. The preview worked with the print statement in there. Tricky...

Improve this answer

Comments

0

I already had SSIS package that was running well and writing to file properly. I changed the query and it stopped writing anything in text file even though i have records which i can see in preview. Solution : Sometimes you have query that has USE database command before the query begins.. remove that command and it will work. Hope it helps someone. Cheers.

Improve this answer

Comments

-1

I needed to choose the Connection Manager node in my Data Flow Task and set the ConnectionString property with the name of the file I wished to create; without this field filled out, no file appeared.

Improve this answer

2 Comments

You used the flat file connection manager to make an Excel output like the question?
Sorry, edited for clarity. Multiple source types use Connection Managers with the ConnectionString property.
-2

I find if I reopen my script component and select "run code analysis" it does a slower 'rebuild all' (Visual studio 2017) which 'wakes it up' again and then the rows are processed. Overall though SSIS/VS 2017 is a nightmare with memory errors, crashes which wipe out the whole csproj and folder structure. I have had to recreate the project dozens of times.

Improve this answer

1 Comment

So, take backups?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.