Somehow I'm not getting dtsrun to work.
The server is local
The name of the package is ImportHosttrncs. It is saved under Local Packages.
There is no password.
Is this syntax correct?
dtsrun /Slocalhost /PImportHosttrncs
I recommend you try the DTS newsgroup microsoft.public.sqlserver.dts
-Jamie
HI, we have something like 120 packages that need to be "upgraded" to a newer version of a template. Basically, we need to add a bunch of variables of various types (12-15 variables). Is there a way to open the package in a script task and add those variables programmatically? Or is there another way to do it (e.g. modify the dtsx file)?
Thank you,
Ccote
There's no way to do this using the Designer. However a package is just an XML document so you try adding a new variable by processing those XML documents using XQuery.
If you work out how to do that - reply here and let me know 
-Jamie
|||
HI Jamie, I have been able to achieve something interesting by using a script component with the test code below:
Public Sub Main()
Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim packagename As Object = Dts.Connections("ChildPackage").AcquireConnection(Nothing)
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
If Not package.Variables.Contains("VarTestNum") Then
package.Variables.Add("VarTestNum", False, "User", 0)
application.SaveToXml(packagename.ToString, package, Nothing)
End If
Dts.TaskResult = Dts.Results.Success
End Sub
The basis of the code I am using comes from Brian Knight web site.So far, I am not able to specify the type of the variable; It seems that SSIS resolve it using the Value method parameter (last one). I do not know if it will bw able to parse a date value correctly. But at least, if all I have to do is to change a type of a couple of variables in all package, the worst is done.
Ccote
|||You're right about the variable type It is determined by the type of the object that you pass in that parameter.
-Jamie
I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.
I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.
I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.
I'm wondering if I need to do a For Each loop looking at an ADO object?
Any suggestions?
Stephanie
SBowe wrote:
I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.
I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.
I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.
I'm wondering if I need to do a For Each loop looking at an ADO object?
Any suggestions?
Stephanie
I suggest you use the Import Wizard to do this for you. I think you can configure it to build the tables for you if they are not already there.
-Jamie
|||
Jamie,
Thanks for the suggestion. However, that won't work for my environment. Specifically, my company requires that I create a scheduled job. So I then must utilize a package.
Here's where I get the flaky results using the SQL transfer object: I only want the non-system tables. So I set the All Tables property to False and then select the tables I want from the Tables Collection property. However, the package then fails when I run it and tells me it cannot find the tables from the source. This is mind-boggling since it allowed me to pick the tables from a list of tables.
I'll keep digging.
Thanks again,
Stephanie
|||Stephanie,
If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).
Rafael Salas
|||Stephanie,I would advocate NOT using the transfer objects task.
This has some "features" (apparently to preserve sql 2000 compatability) that means the tables will not be transferred over accurately.
Specifically, you may find your transferred tables lose default values or identities
see my thread on this here|||
Stephanie,
If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).
Rafael Salas
The import / export wizard will also not setup the tables correctly - see the post I linked to in my post above
I did ran the SSIS package using ctrl+F5 and also from command prompt but still it's quite slow.
SSIS uses SMO to access to server and both are running on 2005
ThanksUse the "Table or view - fast load" option in your OLE DB Destinations.|||sorry for my ignorance, but is this an SSIS property? and if yes where can I find it
Thanks|||In the OLE DB Destination, it is a drop down option titled "data access mode". Double click on the OLE DB destination and it's on the main page.|||the problem is that I am using the transfer SQL server object task which it uses SMO by default not OLEDB. unless there is another way to copy the content of a table.
p.s. the tables do not exist on the remote server the transfer SQk server objects task, creates the table as well as copying the content.
cheers|||
Kolf wrote:
the problem is that I am using the transfer SQL server object task which it uses SMO by default not OLEDB. unless there is another way to copy the content of a table.
p.s. the tables do not exist on the remote server the transfer SQk server objects task, creates the table as well as copying the content.
cheers
Remember that SSIS is a data manipulation tool - not a schema manipulation tool. Hence, there isn't THAT much support for moving schema objects about.
If I were you I would create the tables using conventional methods (i.e. CREATE TABLE scripts run from the Execute SQL Task) and then use data-flows to pump data between them. This will not be slower than DTS. It will be alot more maintainable as well.
-Jamie
|||Thanks for your advise,
and then maybe use the data flow to push the data across.
Thanks again|||Data flows don't handle changing metadata, unless you are building them dynamically.|||Thanks , but even if I build the metadata for the tables in the destination in the control flow (which is not a problem) then I should be able to feed the data with the dataflow.
The problem is I have different tables and they change each time so I should be able to write one generic dataflow and change the parameter with a script each time I'm running.
cheers|||
Thanks , but even if I build the metadata for the tables in the destination in the control flow (which is not a problem) then I should be able to feed the data with the dataflow. The problem is I have different tables and they change each time so I should be able to write one generic dataflow and change the parameter with a script each time I'm running.
cheers
As I think we have discussed on other threads - you can do this.
Apologies if I've missed any of your replies. The alerting functionality of these forums is broken (for me anyway).
-Jamie
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
What defect are you referring to in "Transfer Objects" task?|||Check this out for the defect:
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=336f6832-f68c-4a7f-be74-3e62e1310609
MShah
|||This bug was specific to using "SQL Server Authentication" at the destination of the transfer. It has been fixed in SP1.So, to copy data from one table to the other, you should be able to use Transfer task with "Windows Authentication" at the destionation if you are using SQL Server 2005 RTM. If you have SP1 installed, you should be able to use "SQL Server Authentication" or "Windows Authentication".|||
Thanks. I will upgrade to SP1. We only use SQL Server Authentication here, no windows / mixed mode.
sqlJust want to ask how can I copy a DTS Package? Something like "Save As"...saving a DTS Package to another package name in the same server, or copying a DTS package to another server.
What I am doing right now, is I create the whole package again just to have another copy of it.
Same question with a Job.
Thank you so much.Hey,
If you are using Enterprise manager, double click the package( or right click on the package, there will be a "Design package" option), it will open DTS Package designer, there you will find a menu under the main menu, click on Package and there will be a Save as... command there|||Hi Galexyus,
Thanks for taking the time to answer my query.
I found the "Save As" command already. What am I doing?
It's just in front of me, right before my very eyes. =)
Many thanks to you.
God Bless.
I need to use a value retrieved in one data flow in the second data flow. What's the best way to do this?
How do I copy the column retrieved to a variable so I can use that variable in the second data flow?
Data Flow implies multiple rows, which doesn't naturally fit with a single variable.
Normally I would be using an Exec SQL Task to populate a variable from a table. You could use a Script Component in the data flow, or perhaps the Recordset Destination if there are several rows.
If reading just one value/line from a file for example, then I'd just use the Script Task.
try copying a connection manager from one package to another
it does not work
e.g. excel connection manager
it loses the name of the connection manager and also the file you are pointing to
so it is basically no different to 'add new connection manager'
also
try copying the name of the connection manager to another connection
select connection, hit F2 (edit) ctrl C,
try pasting!
in order to paste you have to right click on the selected text and select copy from the context menu
this is not a bug
this sloppy, poorly tested software
So get busy posting these bugs over at http://connect.microsoft.com/sqlserver/feedback.