June 12, 2012 at 1:23 pm
I have a question about SSIS. I have an export package that we run monthly. I is simple, just an OLE DB Source feeding a Flat File Destination. We have a SQL query in the OLE DB Source. This has been running fine for several months.
A change was requested to the SQL query to add another condition in the WHERE statement. In SSMS it runs fine, returns about 414K rows. In the editor box, the preview returns the 200 rows like it should.
Running the package in debug returns all green boxes, no errors or warnings. But it shows there were zero rows written to the file. And the file is zero length.
The Preview button in the Flat File Destination editor box returns zero rows as well. It just looks like no data is being passed from the query in the OLD DB Source to the Flat File Destination.
I've tried to create a new data flow with a different file name and that returns zero rows as well. Again with no errors or warnings.
What I did was open the existing OLE DB Source editor, selected the SQL query, deleted it and pasted in the new SQL query. After doing that, the package runs fine but no data is written to the flat file. The other 11 files (from the other data flow tasks) are populated and look OK. The query is the correct one, the SQL query columns are all matching output columns in the flat file, same operator is used so there shouldn't be a permissions problem.
I am out of ideas what has happened and don't know what else to look for.
This is a SQL 2008 R2 Enterprise (production) server running:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Anyone care to give me a hand on what I can try next to fix this? It seemed like a simple process, it worked for another data flow change without complaints but this one doesn't report an error but doesn't give me any data.
My knowledge of how this works is pretty limited, we only have two monthly SSIS packages that are run once a month, neither are complex, just extract some data and write it to flat files for FTP to the requesting agencies.
Thanks!
Norman
June 12, 2012 at 1:46 pm
Couple of things. If it won't write while you're using it in BIDS, then you've got a pretty good shot to troubleshoot. First thing, put a data-viewer between the Source and Destination. If it's making it to the data viewer, then you've got a problem on the target. If you don't the problem's from the source.
If it's the target, make sure error rows aren't being redirected or ignored.
If it's on the source, we'll have to dig into the settings and similar, and again, check for errors.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 12, 2012 at 2:27 pm
Thank you for the response Craig,
Yes, I can repeat this at will in BIDS.
I've never used Data Viewers before, so this may take a bit...
I found a reference and added the data viewer (the Grid variety) and set it up (just hit the Add button and accept the defaults). The Configure gives me the list of columns in the left hand Displayed Columns. But I don't see anything in the data viewer. But I'm not really sure what I should be looking at. Or maybe nothing appears in it, nothing pops up in the data flow tab. Just the two boxes turn green and the little data viewer icon between them.
Am I dong this right? Or have I missed a step along the way.
Thanks again,
Norman
June 12, 2012 at 2:55 pm
You're doing it right. If you're not getting any data in the viewer during the test run, then the original query isn't returning data to the process. Check the OLEDB connection and make sure its settings are right. Then doublecheck the source connection and look for what it does with errors if anything.
Your source, somehow, is fouled up. Worst case scenario, delete the objects feeding the source and rebuild them, occassionally SSIS gets screwy and gets bugs under the hood you can't see.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 12, 2012 at 3:32 pm
I use the same OLE DB connection for the other 10 or so steps in he package and they all work. I've tried creating a new data flow and that fails in the same way.
Did I mention that I really am not a fan of SSIS? :-P:-P
So it looks like I get to rebuild the entire package and test it before the 15th... This should be fun, can't take more than 10 hours to get all the parts walking together again...
Unless someone has another path to try, I guess my evenings are booked the rest of the week...
Thanks Craig, I appreciate the help.
Norman
June 12, 2012 at 4:33 pm
n.heyen (6/12/2012)
I use the same OLE DB connection for the other 10 or so steps in he package and they all work. I've tried creating a new data flow and that fails in the same way.
That I missed earlier. That shouldn't be faulty then.
I'm stumped. Usually when things go buggy into the data viewer something ended up messed up in the connector metadata. If you rebuilt this flow from the ground up, which you mentioned you did and slipped my mind, then that isn't it. It means there's a root issue of some kind. On a side note, can you try building everything from scratch in its own package and seeing if it'll work there? That'll let you know if you definately if a rebuild will even help you.
From there, the question is why is the data viewer blank when the source will preview. This assumes a change of something at... runtime...
Errr, dumb question, but can you confirm that nothing got created in the wrong schema over at the source database? I'm wondering if the two logins are seeing different data table sources or something along those lines. It's the only conclusion without going after your system directly that I could come to at this point.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 12, 2012 at 8:45 pm
The security isn't all that sophisticated, there is only one schema (this is a database provided by the vendor) so that can't be it. I've used the same credentials to run the package as I use to run the job. I've tried to run it as SA and as me (I'm the DBA). I really don't think there is a security issue but I'll check in the morning.
I had to change two of the queries, one worked fine and then there was this one. It just doesn't make sense.
I'll see if I have a backup of the working package and see if that still works and try again from that point. This really shouldn't be this hard; it is simple package that I made a simple change to. I mean, all the new query does is slightly refine what data is selected by adding another bit in the WHERE clause, no new or changed columns names. It reports as running fine, the boxes all turn green, nothing is generated in the logs, debug doesn't complain of anything, nothing I can see 'looks' wrong other than there is no output.
It just doesn't make sense to me, I can't see what isn't working or figure out why it isn't working. And the worst part is I really don't think anyone looks at teh data once we send it out, but that is an entirely different issue... :crying:
Anyway, thanks again for the suggestions and time. t does make me feel better that I'm not a complete idiot for missing a really basic step. Hopefully I can assist you at some point.
Norman
June 13, 2012 at 12:10 am
Are you using configurations at some point?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 13, 2012 at 8:58 am
Unfortunately, no, I am not using configurations. Just a simple basic extract and write to a file extract. The whole point is ti correctly format the output for the vendor and have it automatically happen on a schedule. So I don't have to worry about it every month. At least that was the plan...
Norman
June 13, 2012 at 12:30 pm
Believe it or not, I think I found the answer. It looks like the problem started when I copied the new SQL query into the OLE DB task. When I replaced it with the old query, the package ran like it should. That sort of pointed to something odd about the query itself.
Strangely this query ran fine in SSMS.
So I copied the query from SSMS into Notepad++ and back into SSIS and all was good again. All I can figure is there was a bad character in the query that SSMS ignored but SSIS objected to. I've found Notepad++ returns really clean text, stripping out a lot of odd junk. Other text tools might work as well but this is the one I use.
Very strange and I really can't find what was wrong with the SSMS query but that is about all I can think of.
Thanks again to everyone that offered suggestions. I did get to learn a bit about Data Viewers; these might be handy in the future.
Norman
June 13, 2012 at 12:42 pm
Awesome news and thanks for posting back with your solution, hopefully it'll help someone else one day.
I've seen that before, but usually only when you've got a linebreak of some kind in the middle of a text entry, and even then rarely. For some reason I'd thought you were calling a proc, bad reading comprehension on my part.
Again, thanks for the feedback and good luck!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2012 at 12:59 pm
Odd. Never had this issue before, but good to know Notepad++ can come to the rescue 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 3:51 pm
Just in case anyone else runs across this little bit fo SSIS frustration, what we found was this:
The very last line of the SQL query did not have an ending CR/LF pair.
Added a CR/LF to the end is what actually solved the problem.
Apparently SSMS and the preview in DB OLE source doesn't care and the SSIS parser doesn't care. And the SSIS package runs without generating an error or warning. But SQLCMD doesn't like it and must just ignore this and skip the query itself. No data was returned from the query to be loaded into the file. But no errors or warnings are generated.
Just a final follow-up of the final reason why we had this problem.
Norman
June 14, 2012 at 11:55 pm
n.heyen (6/14/2012)
Just in case anyone else runs across this little bit fo SSIS frustration, what we found was this:The very last line of the SQL query did not have an ending CR/LF pair.
Added a CR/LF to the end is what actually solved the problem.
Apparently SSMS and the preview in DB OLE source doesn't care and the SSIS parser doesn't care. And the SSIS package runs without generating an error or warning. But SQLCMD doesn't like it and must just ignore this and skip the query itself. No data was returned from the query to be loaded into the file. But no errors or warnings are generated.
Just a final follow-up of the final reason why we had this problem.
Norman
Good to know. Thanks for posting back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 14, 2013 at 1:33 pm
I ran into the same problem. Stand-alone query produced results but when put into a Data Flow task, the target flat file came up empty. Nothing I did with in Visual Studio fixed the issue. So I started looking at the query. When I dumbed the query down to selecting literals, it worked. My original query truncated a table, loaded that table, and updated that table and then did a union of two selects. I created an Execute SQL Task and used it for the table stuff. The Data Flow Task got just the select stuff. Worked! Only took 2.5 hours. Thanks for this Post; it did give me a clue.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply