SSIS Advantage 时间戳转 SQL 日期时间
我是 SSIS 2008 的新手，目前正致力于将数据从旧的 Advantage 数据库迁移到 SQL Server 2005。我使用带有源和目标的数据流对象设置了 SSIS 包。复制大多数列效果很好，但是当我尝试将 Advantage Timestamp 列复制到 DateTime 字段时，出现许多错误。我相信它们都是溢出错误，这在 Advantage 如何存储时间戳信息中是可以理解的。我的问题是将此列转换为 SQL Server 2005 中正确的 DATETIME 字段的最佳方法是什么？
经过一番思考，我觉得我可以将 Advantage TimeStamp 列复制到 varchar 列中，然后运行一个 sql 脚本来为我进行转换。我想知道是否有更优雅的方法，或者此类问题的正常解决方案是什么。
感谢您的所有帮助和建议！
I am new to using SSIS 2008 and am currently working on migrating data from an old Advantage database over to SQL Server 2005. I set up the SSIS package with a dataflow object with a source and a destination. Copying most of the columns works great, but when I try to copy the Advantage Timestamp columns over to a DateTime field, I get many errors. I believe they are all overflow errors, which is understandable in how Advantage stores the timestamp information. My question is what is the best way to convert this column into the the correct DATETIME field in SQL Server 2005?
Doing a little thinking I felt I could copy the Advantage TimeStamp column into a varchar column and then run a sql script that would do the conversion for me. I was wondering if there is a more elegant way, or what a normal solution to this type of problem is.
Thank you for all of your help and suggestions!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在数据流中，您可以使用派生列转换，您可以尝试将 Advantage Timestamp 转换为 SQL Server DATETIME (DT_DBTIMESTAMP) 数据类型（请注意，这不是 SQL Server TIMESTAMP 数据类型。）在派生列转换中添加一个新的列并输入以下表达式以转换数据类型。
如果失败，则可以将类型转换为字符串（DT_STR、<>、<>），然后使用字符串函数操作该字符串，然后在单个表达式中将类型转换为 DT_DBTIMESTAMP。
In your Data Flow you can use a Derived Column transformation you can try casting the Advantage Timestamp to a SQL Server DATETIME (DT_DBTIMESTAMP) data type (note that this is not the SQL Server TIMESTAMP data type.) In the Derived Column transformation add a new column and enter the following expression to convert the data type.
If this fails, then you can do the type cast to a string, (DT_STR, <>, <>), then manipulate the string using string functions and then type cast to the DT_DBTIMESTAMP in the single expression.
Timestamp是一种数据类型，与日期或时间数据无关。Timestampis a datatype and has nothing to do with Date or Time data.