Audit Table in Execute SQL Task
Use SqlSourceType as 'Direct Input' only. While using INSERT statement, place a place holder (parameter) for number of rows using '?' mark as below and provide parameter mapping in 'Parameter Mapping' tab with that variable.
INSERT INTO dbo.ESG_Cust_Audit values( 'Customer file loaded', ?, getdate())
November 18th, 2011 9:18pm
Hello All,
I have created a Package.The package has Data flow task and Execute SQL Task.The data flow task is getting the data from flat file and importing into the sql server table (It is everyday task, So I scheduled in the SQL Agent).I kept Execute sql task after
data flow for logging purposes. I have a an Audit table in the data base.Once the data flow task is fninished it will go to execute sql task and insert the records in the audit table.My audit table has the columns like these audit_key(identity column) , No.
of records imported, Date_started.I have question regarding no.of records column. I have given as select count(*) from the table. But it wont work. Because I need to have in that column as no. of records exactly imported on that particular day not how many
records in that table...Please help regarding this..
Regards,
Srisql
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 10:59am
I'm not really following what you're trying to do, but you could add a
ROW COUNT task, get the number of the inserted rows into a variable and pass that as a column to the other table..
let me know if you need more help , or you help me by explaining more.
November 19th, 2011 11:06am
Jason has the answer. Only difference is the component's name, it is ROW COUNT (an SSIS data flow component) and not ROW COUNT TASK. Save the record count in a variable just before the destination component in th data flow task and use the same variable
in the execute sql task to insert into Audit table.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 11:25am
I am sorry for the confusion. I have 1 flat file called customer .Everyday I will be having a new customer file .The names are like this customer_2011_11_17 .I have table for this in SQL Server.The table name is customer_test .I scheduled
this package to run daily to import new customer file respective table. I have an audit table in the database to keep track how many customer records imported today . I dont know how to get this count.
Audit table has these columns audit_key,operation, no.records imported, Date_started. I have given execute sql task after data flow task. In the Execute sql task I have given the sql statement like this
INSERT INTO dbo.ESG_Cust_Audit
values( 'Customer file loaded',
(SELECT count(*) from customer_test),
getdate()
)
But it wont work.Because for example I have 6240 records in customer file and if I run this package 6240 records will be imported into customer_test table and in the audit table the no.of records imported column should have 6240 as the value..Tomorrow I
will be having a new customer file and if that file has 5000 records and then if I run this package 5000 records will be imported into the customer_test table and in the audit table the no. of records imported column will have value 6240+5000 = 11240. I dont
want the summation value.I just want the second record as 5000 not 11240.I just want the no.of records imported today but not the overall records in the customer_test table.
Hope you understand better now...Please let me know if you have any questions.
Thanks
November 19th, 2011 11:30am
Jason has the answer. Only difference is the component's name, it is ROW COUNT (an SSIS data flow component) and not ROW COUNT TASK. Save the record count in a variable just before the destination component in th data flow task and use the same
variable in the execute sql task to insert into Audit table.
Nitesh Rai- Please mark the post as answered if it answers your question
True, I've this bad habit of calling anything in SSIS a TASK,
Ahhh and every package is a FLOW ...I keep confusing people, lol :)
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 11:32am
Then what I suggested would work...although you should have a better design in your load table, such as passing a jobID or even a load data and you could identify the customers by load.
I would recommend adding a LOAD DATE column to your table, just to help you in the future, when you need to delete a day or two worth of load, if something wrong happens :)
November 19th, 2011 11:35am
I dont want the summation value.I just want the second record as 5000 not 11240.I just want the no.of records imported today but not the overall records in the customer_test table.
Rowcount will capture the number of records that are imported during that particular load. Why are you using COUNT(*)? Use the variable used inside the row count component.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 11:36am
@Jason --- That would be a good column to be there in that table... :)
@Nitesh --- I have used row count transformation and I have defined a variable to hold the count.How can I use that variable in my query?
November 19th, 2011 12:00pm
INSERT INTO dbo.ESG_Cust_Audit
values( 'Customer file loaded',
(SELECT count(*) from customer_test),
getdate()
)
How can I use row count variable in this...That variable value should be in my second column...I am sorry I am new to SSIS :(
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 12:01pm
I have selected sqlsource type in execute sql task as variable. I selected the variable. But my table has 3 more columns so I need to write a query. Because I need to populate the audit table. the columns are
Audit_key
Operation
No.of records Imported = (Value from row count)
Date_started
I will get the value for no.of records imported from row count.But I need to write a query for other 3 columns. please assist.
Thanks
November 19th, 2011 12:18pm
Use SqlSourceType as 'Direct Input' only. While using INSERT statement, place a place holder (parameter) for number of rows using '?' mark as below and provide parameter mapping in 'Parameter Mapping' tab with that variable.
INSERT INTO dbo.ESG_Cust_Audit values( 'Customer file loaded', ?, getdate())
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 1:07pm
srisql,
I think you can either use the
rowcount component placed right after the flat file load, or use a Script Transfrom as explained in this post:
http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspxArthur My Blog
November 19th, 2011 1:13pm
I have selected sqlsource type in execute sql task as variable. I selected the variable. But my table has 3 more columns so I need to write a query. Because I need to populate the audit table. the columns are
Audit_key
Operation
No.of records Imported = (Value from row count)
Date_started
I will get the value for no.of records imported from row count.But I need to write a query for other 3 columns. please assist.
Thanks
Alright, you just need to configure the ROW count to drop the count to a variable, then use an execute sql task to drop the 3 columns into the audit table...
I would also get the failed records count , just in case (optional)
see the attached screen shots
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 2:38pm
Execute SQL TASK
November 19th, 2011 2:38pm
Hi,
Auditing with a table is quite needed for the package to keep track, you can look into the article to do a real time scenario on how to use the audit
http://beyondrelational.com/blogs/sherryli/archive/2010/09/21/ssis-etl-audit-table-with-variables-passed-to-sql-task.aspxThanks Karthikeyan Anbarasan http://f5debug.net/
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2011 4:16am
Thanks so much for replying back. I have set the variable and assigned to execute sql task. My SQL Statement in sql task is
INSERT INTO dbo.ESG_Cust_Audit(operation
,Num_Records
,Date_Started
,File_Name)
values( 'Customer file loaded',
?,
getdate(),
'Data_Mart_Customer_' +CAST( (YEAR(GETDATE())) AS varchar(5)) + '-' +CAST( (MONTH(GETDATE())) AS varchar(3)) + '-' + CAST((DAY(GETDATE())) AS varchar(2)) + '.csv')
In the parameter mapping I have selected the variable that I have defined rowcount transformation, Parameter name I have given as count and parameter size as 0.
In the result set I have selected the variable and gave the result name as my filed name that is num_records.I am getting the below error.Please assist.
[Execute SQL Task] Error: Executing the query "INSERT INTO dbo.ESG_Cust_Audit(operation
..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection
not established correctly.
Thanks
November 23rd, 2011 12:39pm
Thanks so much for replying back. I have set the variable and assigned to execute sql task. My SQL Statement in sql task is
INSERT INTO dbo.ESG_Cust_Audit(operation
,Num_Records
,Date_Started
,File_Name)
values( 'Customer file loaded',
?,
getdate(),
'Data_Mart_Customer_' +CAST( (YEAR(GETDATE())) AS varchar(5)) + '-' +CAST( (MONTH(GETDATE())) AS varchar(3)) + '-' + CAST((DAY(GETDATE())) AS varchar(2)) + '.csv')
In the parameter mapping I have selected the variable that I have defined rowcount transformation, Parameter name I have given as count and parameter size as 0.
In the result set I have selected the variable and gave the result name as my filed name that is num_records.I am getting the below error.Please assist.
[Execute SQL Task] Error: Executing the query "INSERT INTO dbo.ESG_Cust_Audit(operation
..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection
not established correctly.
Thanks
OK,make sure "result set is NONE", don't define anything in resultset unless you need anything back from the query.
and while you setup the parameters, set your variable count, direction INPUT, Data Type LONG, Name anyname!!, Size is 0
Free Windows Admin Tool Kit Click here and download it now
November 23rd, 2011 12:50pm
Yeah.Thanks a lot Jason. It worked.
November 23rd, 2011 1:07pm