Need help in splitting using Rowcount
Hi All, I am somewhat new to SSIS, and working on a package which combines combines the data from 3 different sql queries using Union All, then checks the Row Count and decides whether to create the destination flat file or now. My intention is to skip the creation of a blank file in case if no rows exist in the result set, and create it only if data rows are passed. My current data flow sequence is as follows: 3* OLE Db source Union All RowCount Conditional Split I am able to get the data past the RowCount component but unable to use conditional split transform to split the output based on Rowcount, since it does not let any data through based on the value of the Rowcount variable. I have seen some scenarios that use Script component to get the split done, but not sure how to compare the variable value, and if any input columns need to be passed as inputs. Can someone please point me on how to get this done? Thanks Akhil
October 18th, 2010 4:43pm

I would use Precedence Constraints that check a variable value containing the record count instead (my onion). An example could be this: http://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 5:00pm

In all the three sources, use Select 1 as DefaultColumn,col1,col2...from tablename as source. Now use a conditional split and check for default column as 1 like : defaultcolumn==1. Take all the valid output to wherever you want. Nitesh Rai- Please mark the post as answered if it answers your question
October 18th, 2010 5:25pm

You cannot do this in a single data flow task. The row count value will not be saved to the variable until the end of the task, by which time your data has already passed through the split, and a file will have been created. A simpler approach would be to accept that a file is created regardless, but then use a second task to delete the file when the Row count is zero. You could use a file system task to do this delete. The choice of when to run the file task would be controlled using a precedence constraint with an expression, @MyRowCountVar == 0.http://www.sqlis.com | http://www.konesans.com
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 5:25pm

Thanks for trying to help guys. I have now changed my strategy to selected data from a single table only based on certain conditions. I have tried Darren's approach to delete the files, but for some reason, it does not delete all the files that match the precedence constraint condition of @rowcount == 0. The non deleted files are not the same every time and their number varies as well. I have already verified that my query does not return any data for those specific instances. Another issue is that the data type bit is exported to csv files as TRUE/FALSE for 1/0 respectively. I tried converting it to single byte , double byte signed and unsigned integers, but doesn't work. It works fine if the data type of the base table is changed to tinyint (which I cannot do). Can you please advise on this issue? Will it possibly work if I populate a variable in sql context with value of @@ROWCOUNT and then pass it to dts package using a Script component? It might sound sneaky, but I'm not really a VB guy, so just curious. :) Thanks!!
October 21st, 2010 2:38pm

Correct me if I am wrong: if you want to see true/false in the output then you need to use a derived column with an expression to override that. Revisit your logic if the end result of files deleted is incorrect. Perhaps express it here. The script component does not seem necessary, why not Execute SQL?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 8:28pm

You described creating one file. If you have a Row Count Tx that captures the number of rows in that file to a variable, then that variable can be used to control the execution of a File System Task that deletes the file. One file, one row count and variable, one task to delete the file. How can it go wrong? You now describe having multiple files, which is rather confusing. Using @@ROWCOUNT is not an option, as you imply using it as part of the source select for which there is no support. You could use an Execute SQL Task to run a basic SELECT OCUNT(*) FROM table and store that to a variable.http://www.sqlis.com | http://www.konesans.com
October 22nd, 2010 12:00am

Apologies for the confusion about the number of files. Here is the logic I plan to implement: 1. Read a Record-set into a table using Recordset reader ie Data Flow 1 2. Based number of rows selected, start a loop where the selected columns are being passed as a parameter to query in the loop. 3. Foreach row execute loop. Inside the loop, there are 2 tasks a. Data Flow contains Export Query with dynamically looping parameter > RowCount > Flat file destination. b. file System task executes only when @rowcount ==0, deletes the files using the same connection string as earlier Ques1. Do I need to use a transform to get the bit datatype to be displayed as it is, instead of text Ques2. Where would I be able to use Exec sql to get rowcount or any other approach.
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2010 8:21am

Within the loop (3) you have a data flow task (a) that creates a file. If that file is empty you delete the file (b). That is the bit you need to get working. If it works for one loop iteration, then it should work for all. Focus on that section, the loop is irrelevant as this section needs to work as a "unit" regardless of how you drive it. You do have the file system inside the loop I hope? The constraint with the expression is between the data flow (a) and task (b) You said it didn't delete all the files it should do, so debug it. Set some break points. Make sure that the variable being set by the data flow task (a), matches the number of rows in the file. The variable set by the row count transformation shouldn't be used for anything else apart from the precedence constraint expression that controls the delete file task (b). Ques1 - I don't understand what you are asking. Ques2 - Instead of creating the file and deleting it, you could run a SELECT COUNT(*) to test if you should create it or not. You would put the Exec SQL Task in front of the Data Flow Task (a). Use constraint between the the Exec SQL Task and the data flow (a). The SELECT COUNT(*) should mimic the source query in your data flow (a) http://www.sqlis.com | http://www.konesans.com
October 22nd, 2010 11:28am

Thanks Darren. I was able to solve the file problem using the Exec SQL task. Its posxible that the connection string used by the file system task may have been a problem, which I will check later. Ques1 Elaborated: I have a few columns in the query which are of bit datatype in the source tables. After exporting these columns to a .csv file, they are automatically interpreted as TRUE /FALSE for values 1/0 respectively from the table. The datatype in the flat file destination shows as Boolean type, so I tried forcing it to be unsigned single / double byte integer, but it still doesn't work the way I would like it to. The numbers are interpreted correctly as 1 or 0 in the file if I change the datatype of the column to a tinyint. I am not sure how to correct the problem. Can you please help.
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2010 8:49pm

Hey Guys, Would anyone be able to help me out on the datatype problem?
October 28th, 2010 2:35am

Format the data to match the output you want in the pipeline. Use a Derived Column or Data Conversion to convert the DT_BOOL to DT_I2, which mimics the bit to tinyint conversion you have already tested. When configuring the Flat File Destination and associated Connection Manager make sure you exclude the orginal column and include the new one. http://www.sqlis.com | http://www.konesans.com
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 4:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics