27 Row Count Transformation in SSIS | RowCount Transformation in SSIS

[@magedhendam6511]

nice can i ask u qustion please if i have distination row by row and the etl get failure so some data transport and some not so how can i know the data isent deliverd to the distinatian and how i can make etl run faster

[@learnssis]

Hi, thanks for your question. If both your source and destination are sql, and your data flow got failed, only then you can identify what are the records not loaded to destination, if your source is csv and destination is sql if process got failed in between then you can't tell which records are not loaded yet. 1. If you are loading the data to sql, then make sure in the ole db destination, fast load option is enabled. 2. try to run multiple tasks in parallel. 3. While loading data to sql tables, make sure they don't have indexes, indexes make them slower. 4. Use proper data type from source, for example flat file source by default uses varchar(50) for all columns. 5. While using SQL as source, try to use most of the conversion on sql query itself, for example merging columns, doing some transformation etc instead of doing it in ssis transformations.

[@hobertjhunjhunwala1515]

Thanks for the video, it's really helpful.

[@learnssis]

Thank you Hobert.

[@oshriamir]

Hi Aquil, thanks for this video. After i ran the pacage , I do not see in the recordcount for the first file the value 0 in table LOGS. can you explain how to fix that ?

[@learnssis]

In the rowcount transformation make sure that you have selected the correct ssis variable of integer type. And after the data flow task, make sure you are using that variable to insert the value into the sql server table. Maybe debug your package and see if the ssis variable holds the correct value for the variable kzbin.info/www/bejne/Z4CwcoGHndSAh68

[@LaRothe]

Thank you very much. Your video helped me a lot.

[@learnssis]

Thank you Laila for your comment, glad it helped.

[@dilipkumar-ck1nc]

In expression builder the code is not there in notepad U show 3 csv files but in google drive there are 2 csv files

[@learnssis]

I have uploaded the 3rd file as well. Also uploaded the expression file as well.

[@DhanyaGowda-l9m]

[Execute SQL Task] Error: Executing the query "INSERT INTO LOGS VALUES ( ' C:\Source\RowCount\Row..." failed with the following error: "Invalid object name 'LOGS'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. i'M GETTING THIS errOR

[@learnssis]

It seems like LOGS table does not exists on the database. You would need to create the LOGS table if you are trying to use it.

[@Mishramultiverse]

Hi Aqil Thanks for this video. Could you pls write the Expression you used in the Sql statement source. bcs when i wrote the same expression got the error like "Incorrect Syntax near D:/folder"

[@learnssis]

After looking at the error from you. "INSERT INTO LOG VALUES ('D:\ssis\container\data1'.csv.0. GETDATE()). It seems you have separated the columns with a dot (.) however you should use comma to separate a column with another one. For example First you will provide the file path from ssis variable like below " '"+ FilePathVariableName + "' , " + (DT_WSTR,12) RowCountSSISVariableName + " , getdate() " So for string SSIS variables first we need to use a single quotes (') and then use double quotes (").

[@mdakhtarkhan4235]

"INSERT INTO AuditLogsFile VALUES('"+ @[User::FilePath] +"',"+ (DT_WSTR,12)@[User::RowCount] +",GETDATE())"

[@faizanmajid2922]

what if the source is sql table? how to insert name of table in the query??

[@learnssis]

If your source is sql table then it won't make any difference as Row Count Transformation is not dependent on source. If you want to insert the name of table then instead of file path, you can provide sql table name. If your source is a sql table then even you don't need to use a Row Count Transformation, after the data flow task use an execute sql task and there write an insert query to insert the table name and number of records to audit log table. In this example we are creating an audit_log table and then inserting the sql table name Canada and row count of Canada to audit_log table -------- IF NOT (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'audit_log')) create table audit_log(Id int identity, TableName varchar(100), RecordsInserted int, Dated datetime) go declare @rowcount int set @rowcount= (select count(*) from Canada) insert into audit_log values('Canada',@rowcount, getdate()) ------- Above code can be written in a Execute sql task.

[@faizanmajid2922]

@@learnssis Thank you so much sir. I have some other queries regarding ssis packages. Is there any other way to contact you?

[@learnssis]

@@faizanmajid2922 You can email me your questions at [e-mail]

so nice!

[@learnssis]

Thanks for watching the video. Great to know you liked it.

[@dudewheresmycar3618]

Hi. I get the following error message :[Execute SQL Task] Error: Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {19E353EF-DAF4-45D8-9A04-FB7F7798DCA7} failed due to the following error: 80040153.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Please assist

[@SolomonBerhane-vw8uq]

at this point go to execute task result set change the resultset name into 0

[@JosueLopezXIX]

very nice video, how about when the flat file is empty? would it load still?

[@learnssis]

Thanks Josue. If the file will be empty then SSIS package won't fail and no data will be loaded to SQL Server table and the value of SSIS Variable RecordCount will be 0.

[@JosueLopezXIX]

thanks, could you email me privately to [e-mail] i have some other questions. thanks!

[@emmanuele8451]

Does this work with Excel Dynamic Connection Managers?

[@learnssis]

Yes it works.

[@AmitSharma-yi9dr]

hello, can we use variables in ssis 2017?

[@learnssis]

Yeah sure Amit, we can. Thanks.

[@satyamsamala8445]

thank you boss...

[@learnssis]

Glad you liked the video.

[@fasahataziz8420]

Hi Aqil...thank you for the easy explanation. I tried this so the data was loaded to my SQL server table but the LOGs table is empty... below is the error that is being shown, pls advise. would be grateful for your early reply. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO LOGS VALUES (C:\Users\fasah\OneDrive\D..." failed with the following error: "Incorrect syntax near 'C:'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at RowCount: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "C:\Users\fasah\OneDrive\Documents\MSBI Training\SSIS\MyFirstProject\MyFirstProject\RowCount.dtsx" finished: Failure. The program '[24276] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

[@learnssis]

It seems like you are missing a ' single quote before and after the " quote in the file path. 1. When you start writing any code start the code with double quote " and close it with double quote " as well. 2. Any string value will be surrounded by single quote followed by double quote, then put a + sign and then put SSIS variable there and after it, again put a + single, followed by double quote single quote like below ' " + @[User::FILEPATH] + " ' 3. Any Int SSIS variable will be surrounded by just double quote. So put a double quote " then put a + sign, then place the SSIS variable there and put the + sign there and then put the double quote there. You also need to convert the Int to string so that it can be used in the expression. An example is below " + (DT_WSTR,12) @[User::COUNT] + "

[@rupamdhamale9486]

Your video is very good but to much blur not able see what you are typing

[@learnssis]

Thanks for your suggestion, will try to improve.

[@dilipkumar-ck1nc]

Gud explanation but how u remember that code

[@learnssis]

I can see the video and can understand it.