I have a DTS package with a data transformation task (data pump). I’d like to source the data with the results of a stored procedure that takes parameters, but DTS won’t preview the result set and can’t define the columns in the data transformation task.
我有一个带有数据转换任务的DTS包(数据泵)。我想使用带参数的存储过程的结果来获取数据,但DTS不会预览结果集,也无法在数据转换任务中定义列。
Has anyone gotten this to work?
有没有人得到这个工作?
Caveat: The stored procedure uses two temp tables (and cleans them up, of course)
警告:存储过程使用两个临时表(当然,清理它们)
3 个解决方案
#1
3
Enter some valid values for the stored procedure parameters so it runs and returns some data (or even no data, you just need the columns). Then you should be able to do the mapping/etc.. Then do a disconnected edit and change to the actual parameter values (I assume you are getting them from a global variable).
输入存储过程参数的一些有效值,以便它运行并返回一些数据(甚至没有数据,您只需要列)。然后你应该能够进行映射/等等。然后进行断开连接的编辑并更改为实际的参数值(我假设你是从全局变量中获取它们)。
DECLARE @param1 DataType1
DECLARE @param2 DataType2
SET @param1 = global variable
SET @param2 = global variable (I forget exact syntax)
--EXEC procedure @param1, @param2
EXEC dbo.proc value1, value2
Basically you run it like this so the procedure returns results. Do the mapping, then in disconnected edit comment out the second EXEC and uncomment the first EXEC and it should work.
基本上你这样运行它所以程序返回结果。做映射,然后在断开连接编辑注释掉第二个EXEC并取消注释第一个EXEC,它应该工作。
Basically you just need to make the procedure run and spit out results. Even if you get no rows back, it will still map the columns correctly. I don't have access to our production system (or even database) to create dts packages. So I create them in a dummy database and replace the stored procedure with something that returns the same columns that the production app would run, but no rows of data. Then after the mapping is done I move it to the production box with the real procedure and it works. This works great if you keep track of the database via scripts. You can just run the script to build an empty shell procedure and when done run the script to put back the true procedure.
基本上你只需要运行程序并吐出结果。即使您没有返回任何行,它仍将正确映射列。我无法访问我们的生产系统(甚至数据库)来创建dts包。因此,我在虚拟数据库中创建它们,并将存储过程替换为返回生产应用程序将运行的相同列的内容,但不包含任何数据行。然后在映射完成后,我将它移动到具有真实过程的生产盒中,并且它可以工作。如果您通过脚本跟踪数据库,这非常有用。您可以运行脚本来构建一个空的shell过程,完成后运行脚本以放回真正的过程。
#2
0
You would need to actually load them into a table, then you can use a SQL task to move it from that table into the perm location if you must make a translation.
您需要将它们实际加载到表中,然后如果必须进行转换,则可以使用SQL任务将其从该表移动到烫发位置。
however, I have found that if working with a stored procedure to source the data, it is almost just as fast and easy to move it to its destination at the same time!
但是,我发现如果使用存储过程来获取数据,那么同时将它移动到目的地几乎同样快速和容易!
#3
0
Nope, I could only stored procedures with DTS by having them save the state in scrap tables.
不,我只能通过让他们在废料表中保存状态来存储DTS程序。
#1
3
Enter some valid values for the stored procedure parameters so it runs and returns some data (or even no data, you just need the columns). Then you should be able to do the mapping/etc.. Then do a disconnected edit and change to the actual parameter values (I assume you are getting them from a global variable).
输入存储过程参数的一些有效值,以便它运行并返回一些数据(甚至没有数据,您只需要列)。然后你应该能够进行映射/等等。然后进行断开连接的编辑并更改为实际的参数值(我假设你是从全局变量中获取它们)。
DECLARE @param1 DataType1
DECLARE @param2 DataType2
SET @param1 = global variable
SET @param2 = global variable (I forget exact syntax)
--EXEC procedure @param1, @param2
EXEC dbo.proc value1, value2
Basically you run it like this so the procedure returns results. Do the mapping, then in disconnected edit comment out the second EXEC and uncomment the first EXEC and it should work.
基本上你这样运行它所以程序返回结果。做映射,然后在断开连接编辑注释掉第二个EXEC并取消注释第一个EXEC,它应该工作。
Basically you just need to make the procedure run and spit out results. Even if you get no rows back, it will still map the columns correctly. I don't have access to our production system (or even database) to create dts packages. So I create them in a dummy database and replace the stored procedure with something that returns the same columns that the production app would run, but no rows of data. Then after the mapping is done I move it to the production box with the real procedure and it works. This works great if you keep track of the database via scripts. You can just run the script to build an empty shell procedure and when done run the script to put back the true procedure.
基本上你只需要运行程序并吐出结果。即使您没有返回任何行,它仍将正确映射列。我无法访问我们的生产系统(甚至数据库)来创建dts包。因此,我在虚拟数据库中创建它们,并将存储过程替换为返回生产应用程序将运行的相同列的内容,但不包含任何数据行。然后在映射完成后,我将它移动到具有真实过程的生产盒中,并且它可以工作。如果您通过脚本跟踪数据库,这非常有用。您可以运行脚本来构建一个空的shell过程,完成后运行脚本以放回真正的过程。
#2
0
You would need to actually load them into a table, then you can use a SQL task to move it from that table into the perm location if you must make a translation.
您需要将它们实际加载到表中,然后如果必须进行转换,则可以使用SQL任务将其从该表移动到烫发位置。
however, I have found that if working with a stored procedure to source the data, it is almost just as fast and easy to move it to its destination at the same time!
但是,我发现如果使用存储过程来获取数据,那么同时将它移动到目的地几乎同样快速和容易!
#3
0
Nope, I could only stored procedures with DTS by having them save the state in scrap tables.
不,我只能通过让他们在废料表中保存状态来存储DTS程序。