copy many ssis packages between servers

Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
PLTry backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entrie
s
that may need to be changed.
"Piotr Lipski" wrote:

> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I hav
e
> to move there all the data. Including ssis of course. There are about 40-5
0
> packages to do (stored in the database, not in filesystem) and I hope ther
e
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 job
s
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be jus
t
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there an
d
> you should be OK. And just check the SSIS tables to discover similar entr
ies
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
PL|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:

> Sean McCown wrote:
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>

copy many ssis packages between servers

Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
--
PLTry backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entries
that may need to be changed.
"Piotr Lipski" wrote:
> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I have
> to move there all the data. Including ssis of course. There are about 40-50
> packages to do (stored in the database, not in filesystem) and I hope there
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 jobs
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be just
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there and
> you should be OK. And just check the SSIS tables to discover similar entries
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
--
PL|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:
> Sean McCown wrote:
> > Try backing up and restoring MSDB and changing the appropriate fields.
> > We do this with jobs every week to keep 2 servers in sync and this is what
> > we use to bring the jobs up to date. I would imagine that it could be just
> > as easy for SSIS packages, though I haven't tried it officially.
> >
> > UPDATE sysjobs
> > SET originating_server = 'MyServer'
> >
> > So just run that on the new server once you have MSDB restored on there and
> > you should be OK. And just check the SSIS tables to discover similar entries
> > that may need to be changed.
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>

copy many ssis packages between servers

Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
PL
Try backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entries
that may need to be changed.
"Piotr Lipski" wrote:

> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I have
> to move there all the data. Including ssis of course. There are about 40-50
> packages to do (stored in the database, not in filesystem) and I hope there
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 jobs
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>
|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be just
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there and
> you should be OK. And just check the SSIS tables to discover similar entries
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
PL
|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:

> Sean McCown wrote:
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>
sqlsql

Copy Excel sheet to SQL 2005(Dev Edit X64) via SSIS

Ok...

This worked on the laptop I had last week. That laptop has ceased to be. I have a new one, which may be my problem.

I have a package that takes an Excel Sheet, makes a couple of conversions via the Data Conversion object, and places the rows into my SQL 2005 X64 database.

Here are the major exceptions:

1. [Excel Source [2479]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (THERE WERE NOT)

2. [Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Here is the rub... I am running Vista(Weeeee), and Office 2007(Double Weeeeee). Last week I was running XP, and Office 2003.

Is this a wierd Vista permission problem? The "Class not Registered" message is worrying me. Does SSIS work with Excel 2007 installed?

Xig

Xig,

Here is a link to considerations for SSIS on 64-bit computers: http://msdn2.microsoft.com/en-us/library/ms141766.aspx.

One of the things to note is that there is no 64-bit Jet driver, so connections to Access and Excel don't work in 64-bit mode. You can however run your packages in 32-bit mode. The link provided above has additional details.

Thanks,
Patrik

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database Wizard Error- SQL Server Agent cannot execute the SSIS package

I am trying to

copy a database from our company's external SQL

Server(production) to our local SQL

Server(development). The Copy Database wizard fails on the step

"Execute SQL Server Agent Job".
Following is the error in the log file.. Please advise

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Where did you run your package?|||The Copy Database Wizard ran the SSIS package automatically, at the end of the wizard.|||The Copy Database Wizard ran the SSIS package automatically.|||Oops. I thought you were using transfer database task in SSIS. I think you are at the wrong forum. I assume you initiated copy database wizard from sql management studio.Try posting your issue at the right forum:
SQL Management studio: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597172&SiteID=1
SMO: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

Also, before doing that you may want to check if both the source and destination servers support remote connections.|||Thanks for pointing me to the fourm. Yes I am using Management Studio for the Copy Database Wizard. Could you please tell me how I can check to see if the source and the destination servers allow remote connections? Is that a Windows Server2003 setting or a SQL Server2005 setting?
Thanks!|||1. Open "SQL Server 2005 Surface Area Configuration" (in Programs-> Microsoft SQL Server 2005 -> Configuration Tools)
2. Click on "Surface Area Configuraiton for Services and Connections"
3. Select "Remote Connections" option for the instance you are interested in.
4. Select "Local and Remove connections"

In SQL Server Management studio, right click on the instance, select properties, go to connections page and select "Allow remote connections tot his server".

I do not know what the default settings are. So, check both.|||When I click on Remote Connections, I get a message saying"You cannot configure surface area of clustered services by connecting to a computer name. Connect to the virtual server to configure clustered services"

Do you know how/where I can do that?
Thanks again!

copy content of txt file

Hi Guys,

What approach should I use to copy content of a text file. Is BCP capable of doing this? How about SSIS?

Example of the text file's content:

Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90

In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.

Regards,

Lars

Hi,

You could use DTS for this. Define a new DTS package, with your database in source, and a text file for output.

You can use an SQL order to filter your Data (..Where Date = "20060101" AND ....).

The result will be formatted as a CVS file by default. If you need to personalize your output format, you can use ActiveX scripts in transformation tasks to do this

ex :

Function Main()
DTSDestination("Date") = "Date, " & DTSSource("Date")
Main = DTSTransformStat_OK
End Function

|||

hi,

if you know the location of the row you want to copy you can use

the -f anf -l switch of BCP which stands for

-f = the first row

-l = the lastrow

regards,

joey

Copy Column in SSIS

What is the use of the Copy Column in SSIS please help me

give me the example

regards

koti

Hi ya,

Copy column is only there to create new copies of the existing columns. Later you can do some transformation or do some aggregations for the new copies. You can create multiple copies of a column.

Hope this helps.

Cheers

Rizwan

Copy and Paste Minor Irritation

Can anyone explain why when I copy a data element and attempt to paste it it always appears waaay off screen in my SSIS 2005 package? Why won't it paste where I am right-clicking and telling it to go? I hate having to hunt for the element in the flow.

To save you hunting, the components always appear at the lower left corner or the upper right corner (as far as I've seen).

COnverting Numeric data type (Oracle) to Date Data type using SSIS

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

If you are using a query to go against Oracle, you'll likely want to cast that as a varchar and then work with it in SSIS. Not sure that YYYYMMDD will cast to DT_DATE. You'll likely have to substring pieces of that to get it into a date. There are plenty of examples here on this forum for doing that. Just search for "YYYYMMDD."|||

Thanks for the reply. I used SQL on the OLEDB Source and added a TO_DATE expression in the SQL and use that column as input to my destination column.

This worked fine.

RH