How do I write System::StartTime to a table?
Hi, I am trying to very simply insert the System::StartTime into a file. I have placed a watch on this system variable the contents of this system variable within a watch appear valid, e.g. ; - SYSTEM::STARTTIME {18/10/2010 14:30:27} DATETIME I created an Execute SQL Task. I mapped the variable name SYSTEM::STARTTIME as a parameter within a data type of DBDATE. Entered the SQL Statement; - INSERT INTO dbo.CustomSSISLog (StartTime) VALUES (?) Where StartTime has a data type of DateTime. On executing the package I get the error message; - Error: 0xC002F210 at My Package, Execute SQL Task: Executing the query "INSERT INTO dbo.CustomSSISLog (StartTime) VALUES (?)" failed with the following error: "The type is not supported.DBTYPE_DBDATE". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any ideas? Kind Regards, Kieran. If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
October 18th, 2010 4:50pm

Can you try the following? INSERT INTO dbo.CustomSSISLog (StartTime) SELECT ? Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 5:01pm

It is a known kink, please see http://consultingblogs.emc.com/jamiethomson/archive/2005/03/12/SSIS_3A00_-Datetime-variables-don_2700_t-always-do-what-you-expect.aspx, the workaround is there, too.Arthur My Blog
October 18th, 2010 5:04pm

Thanks for the quick feedback SQLUSA, unfortunately I get the same error though.If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 5:15pm

Hi ArthurZ, I have read link; - http://consultingblogs.emc.com/jamiethomson/archive/2005/03/12/SSIS_3A00_-Datetime-variables-don_2700_t-always-do-what-you-expect.aspx So I inserted; - "insert into dbo.CustomSSISLog (StartTime) values ( '" + (DT_STR, 4, 1252) DATEPART("yyyy", @[System::StartTime]) + "-" + (DT_STR, 4, 1252) DATEPART("mm", @[System::StartTime]) + "-" + (DT_STR, 4, 1252) DATEPART("dd", @[System::StartTime]) + " " + (DT_STR, 4, 1252) DATEPART("hh", @[System::StartTime]) + ":" + (DT_STR, 4, 1252) DATEPART("mi", @[System::StartTime]) + ":" + (DT_STR, 4, 1252) DATEPART("ss", @[System::StartTime]) +"')" into Property SqlStatementSource and I get the error; - Error: 0xC002F210 at Unable to Update DATETIME, Execute SQL Task: Executing the query ""insert into dbo.CustomSSISLog (StartTime) values ( '" + (DT_STR, 4, 1252) DATEPART("yyyy", @[System::StartTime]) + "-" + (DT_STR, 4, 1252) DATEPART("mm", @[System::StartTime]) + "-" + (DT_STR, 4, 1252) DATEPART("dd", @[System::StartTime]) + " " + (DT_STR, 4, 1252) DATEPART("hh", @[System::StartTime]) + ":" + (DT_STR, 4, 1252) DATEPART("mi", @[System::StartTime]) + ":" + (DT_STR, 4, 1252) DATEPART("ss", @[System::StartTime]) +"')"" failed with the following error: "The type is not supported.DBTYPE_DBDATE". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Unable to Update DATETIME Any ideas? Kind Regards, Kieran.If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
October 18th, 2010 5:37pm

Did you use the expression that works? Like this: http://www.sqlis.com/post/The-Execute-SQL-Task.aspx ?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 5:44pm

I´ve got an idea.... what is your SQL server versión? The message seem to say that don´t understand Date type. Maybe it's because don´t exist yet (in sql2005)? Try to cast type like (DT_DBTIME) (expression) Tell us your observations.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it.
October 18th, 2010 5:57pm

Instead of using OLEDB Connection, create an ADO.NET connection and use it in the execute sql task. The query would be: Insert Into Table Select @p. In parameter mapping select datatype as datetime and parameternae as @p.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 5:59pm

Focusing on link http://www.sqlis.com/post/The-Execute-SQL-Task.aspx again. I also tried; - insert into dbo.CustomSSISLog (StartTime) values ( SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 19 ) ) To get any sort of date value output I changed StartTime to varchar(100). Task failed: Unable to Update DATETIME Error: 0xC002F210 at Unable to Update DATETIME, Execute SQL Task: Executing the query "insert into dbo.CustomSSISLog (StartTime) values ( SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 19 ) ) " failed with the following error: "The type is not supported.DBTYPE_DBDATE". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Unable to Update DATETIME I am using SQL Server 2005 with SP2.If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
October 18th, 2010 6:07pm

Try using ADO.NET connection manager as I mentioned in my previous post.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 6:35pm

Many thanks for every one's help. I think I'd better be pragmatic for the time being and use; - insert into dbo.CustomSSISLog (StartTime) values ( GETDATE() ) I will dig deeper into the recommendations you have made at a later date.If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
October 18th, 2010 7:11pm

Hi Kiearn, In SQL Server Integration Services(SSIS), a DateTime typed variable will be implicitly converted it to a string, and uses the human-readable localized format such as 1/8/2011 2:02 PM. The human-readable localized format may be not available in SQL Server(The SQL Server may expect format like 2011-01-08 14:06:44.000). That is why the issue happens. To fix the issue, we can follow these steps: Create a new variable in the package. The type of the variable is String Set the expression for the new variable to be(the expression is used to get a format string like 2011-01-08 14:06:44.000): (DT_WSTR, 4)YEAR(@[System::StartTime]) + "-" + RIGHT("0" + (DT_WSTR, 2)MONTH(@[System::StartTime]), 2) + "-" + RIGHT("0" + (DT_WSTR, 2)DAY(@[System::StartTime]), 2) + " " + RIGHT("0" + (DT_WSTR, 2)DATEPART("Hour", @[System::StartTime]), 2) + ":" + RIGHT("0" + (DT_WSTR, 2)DATEPART("Minute", @[System::StartTime]), 2) + ":" + RIGHT("0" + (DT_WSTR, 2)DATEPART("Second", @[System::StartTime]), 2) + "." + RIGHT("00" + (DT_WSTR, 3)DATEPART("Millisecond",@[System::StartTime]), 3) Set the EvaluateAsExpression to be True Now, map the variable in the Execute SQL Task, set the parameter type to be: DBDATE Additionally, Nitesh's solution works well. Thanks, Jin Chen Jin Chen - MSFT
Free Windows Admin Tool Kit Click here and download it now
January 8th, 2011 1:24am

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

Other recent topics Other recent topics