Migration Of DTS Packages To SSIS

Feb 16, 2006

I am in a situation where we are redesigning our Datawarehouse. Currently we have our Datawarehouse in SQL 2000 and we are rebuilding from scratch in SQL 2005. This means that even though we will get the same tables but we are planning to rename each & every attribute to make it more meaningful.

The question is like this:

We can migrate the current DTS packages to SSIS but since all the ODBC connections , field names(attributes) will change is it worth it to leverages the DTS packages ?

Also we convert the julian date to gregorian date in our DTS packages but since SSIS has a feature to convert julian date it would be redundant to migrate the packages and my feeling is to create new packages in SSIS and start on a clean slate.

Is there anybody who was in such situation ?

Please advise.

Adarsh Mathur

View 2 Replies

DTS Migration Wizard, How To Edit MSDB Packages?

Oct 30, 2006

Thanks to Sean for helping me find the migrated packages. I'd like to edit one of the connections on a bunch of packages that I migrated. I see in the Run Package there are configuration options that would allow me to mofify the connection string at that point in time, but I would like to modify the package so it has the new connection string. I was thinking I could access it in BI Dev studio, but I'm not sure how to get to it. I suppose I could migrate to a file and then edit the file prior to importing, but I'm curious if there's a way I can modify the packages that show up under Integration Services/Stored Packages/MSDB.

Thanks,

John

View 1 Replies View Related

Calling SSIS Packages From ASP.NET - Packages With File System Tasks End Abruptly

Jan 9, 2007

I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.

Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.

When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.

I've tried giving administrative permissions to the ASPNET worker process user to no avail.

I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.

I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.

Anybody got any idea on how to go about this?

View 33 Replies View Related

Integration Services :: Remotely Execute Packages On SSIS Server - Packages Are Deployed In File System

Apr 22, 2015

We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.

SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.

We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.

One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.

View 4 Replies View Related

DTS Migration To SSIS

Nov 12, 2007

I have a problem with this ActiveX Script. I need to find out why I'm getting this error message: "retrieving the file name for a component failed with error code ..." I think it has something to do with my global variable setting. Am I right? Help please.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim fn
Dim mm, dd

mm = Month(Now)
dd = Day(Now)

'Padded a 0 in front of the month if its less than 10 to maintain the YYYYMMDD format
If cInt(mm) < 10 then
mm = "0" & mm
End If

'Padded a 0 in front of the month if its less than 10 to maintain the YYYYMMDD format
If cInt(dd) < 10 then
dd = "0" & dd
End If

'Dev Server
fn = "D:Documents and SettingsUserDesktopDTSMigrateSamplesAEC_" & Year(Now) & mm & dd & ".txt"

dim oPackage
dim oConn

set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections("HR Feed")
oConn.datasource = fn

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success
End Function

View 3 Replies View Related

DTS Migration To SSIS

Jan 10, 2006

Good Day,

I am currently in the process of migrating my DTS packages to SSIS---one at a time---and have come accross the following error during the excecution of a "Copy SQL Server Objects Task". The exact error is:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported.".

What I am doing with this particular task is to copy a table(s) from another SQL Server, to the current SQL Server. The original location is a SQL2000 DB table. In the near future the source server for this table will be a SQL 2005 server but not until the current migration has been completed.

I have looked at all of the properties, but can not find anything relating to the db compatibilty level. Is there an easier way? Or a setting that I can change to accomplish what I am trying?

Thanks for any help/suggestions.

View 8 Replies View Related

Migration From DTS To SSIS

Sep 6, 2007

Hi everybody,
I would REALLY appreciate it if some had a link to a good tutorial about migration from DTS to SSIS, but without visual studio . net.
For example I need to use DTS package in sql server 2005 and execute them, I used migration wizard (SQL 2005) but it didn't work because i have in my computer sql 2000 et sql 2005 and the server name is PC2, however when i use the server source PC2 and the server destination PC2 an error message appear "server destination should be a sql server 2005 instance ".

help me please..
Thanks very much anyone response my question.

View 3 Replies View Related

Migration Of DTS To SSIS

Nov 6, 2006

Well..

Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entrieslins for old dataserver.

Is it possible to edit the SSIS pckage using management studio or integration services?

View 1 Replies View Related

SSIS For Data Migration

Apr 5, 2007

Hi guys,

I just moved to SQL 2005 and am facing a difficulty trying to build a DTS Package using SSIS Projects. I need to migrate data from an old Database design to a new Database design in my local pc.

Can anyone provide me some guidelines or tutorials on which components to use and a flow of the entire process which I should follow? I am confused.

Thanks alot in advance, hope to hear from you guys soon.

View 1 Replies View Related

DTS To SSIS Migration Question

Aug 2, 2007

Its probably a little late for this kind of question but, I'm converting several DTS packages over to SSIS and in the conversion I have the ability to run tasks as 'Execute DTS 2000 Package'. My question is, should I use that or do a full re-write of the flows to use the SSIS tasks?

So far I've been removing anything pertaining to SQL 2000 and adding the SSIS tasks. Its becoming very time consuming this way due to some of the DTS packages have a lot of tasks/flows. Is it the best way to do this or should I just use the DTS migration Wizard and then leave the .dtsx files alone once they're created from the wizard?

View 4 Replies View Related

SSIS Migration Between Servers

Sep 21, 2007

Good Day,

I am in the process of moving my ssis package development to a new machine. I have zipped up all of my development/solution files and put them on the new machine...put them in place...but when I try to test them within Visual Studio I am getting a build error that says

Error 3 Cannot create the output file "C:Documents and SettingsmandolinMy DocumentsVisual Studio 2005ProjectsTransfer and Load Enrollment DataTransfer and Load Enrollment DatainCUP Enrollment Data Pickup.dtsx": The device is not ready. . 0 0

Additionally, but related, I can only open some projects within the solution. I am getting the following error when I try to open "some" of the packages within the solution:

The Device is not ready. (Exception from HRESULT: 0x80070015)

Can someone offer some advice on just what is happening here? I would really appreciate any thoughts or advice.

Thank you in advance.

View 9 Replies View Related

Data Migration Using SSIS In SQL 2005

Aug 8, 2006

Greetings,
I am using SQL Server 2005 and I want a clue if there is some good built-in support to do the following.
I want to transfer all the data from one source database to another destination database having same number to tables and schema like source database. I also want to ensure that the old data on the destination database should be secure too.
I have an idea that this can be done through SSIS in coding but can anyone give me a guide, tip, link, sample code that can be helpful.
Thanx a million in advance.
Regards

View 1 Replies View Related

Migration From Sql Server 2000DTS To SSIS

Mar 26, 2008

Hi,
I am going to work on the Migration project(DTS to SSIS).Could you plese suggest me some useful links reg Migration process and Best Practices used for Migration.

Appreciated Assistance/guidence in this regard

View 3 Replies View Related

Regarding SSIS Implementation Difficulties In Migration

May 2, 2008

We would like to Share the BatchJobs functionality before going to the difficulties what we are facing in SSIS package Design and it's implementation.

BatchFile Process Steps:

1.Flat File is coming from the upstream server(DB2 main Frame Server) to the linux box in which the jobs are scheduled using crons.

2.The Flat file is validated whether having header and trailer information.The flat file name itself (eg. APPLWIC00077.dat ) will have it's sequence number ie 00077.This number will be checked against Flat file Header information Which also gives the sequence number information .If both number matches,The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen.

3.The Job will process the coming flat file and Loading it into SQL Server Staging Table.

4.If loading is successful,then Some Sql server Procedure are called to validate the Staging Table Data,Filters,then move the Data from Staging table to Target table.

5.All Process or Steps are tracked,If anything goes wrong,Mail will be sent to Concerned Person.

Utilities Involved:

1. SQL Loader to Load the FlatFile Data into the SQL Server 2005 Database.

2. SQLPLUS to execute the PL/SQL procedures.

3. SENDMAIL or MAILX to send mails to externals.

Additional Information:

The SQL Loader Control File Splits the flatfile data by Position wise in order to load it into the particular column of the Staging Table.No column delimiters.Data are taken position wise (like column1 -> Sequence No , 1-3 -> column2 , 4-8 -> column2 )and checked against a condition By which the particular Staging Table is selected.

Difficulties We face in migration in regard to SSIS Package Design and it's Implementation:

1. We could Receive the flat file using FTP Task and we don't know how to open and validate the flat file data Like What we mentioned in STEP 2.

2. We could load the sample flat file which is properly delimited like {CR}{LF} ,{CR},{LF},semi -colon,comma,colon,tab,vertical bar .

If flatfile is messed up and data need to be processed by position wise (Scenario mentioned in Additional Information) ,We don't know how to implement this in SSIS package with the Limited delimiter option given.

FYI : Flat File columns are not fixed and ragged type.

3. We need logging information like howmany rows inserted, discarded,something in detail..How can we log these information using SSIS.This will help to send detailed mail to Application handler.It should be automated using SSIS.

4. How can we execute Procedures Using available SSIS tasks ( Executing many Procedures which is residing in Sql server 2005 using Single sql file as input to SSIS package)

Thanks in Advance!
Your input is priceless.

Regards,
Prabhu.M

View 4 Replies View Related

Migration From Sql Server 2000DTS To SSIS

Mar 26, 2008

View 6 Replies View Related

SSIS Migration Wizard Question

Aug 24, 2006

I am writing a vb application that is supposed to let the users set the connection string for the datasources in the package. After new connection strings are entered the application is supposed to run 8 packages in a certain order, but I haven't been able to set a new connection string successfully. Is there a way to programmatically modify the connection string of a package's datasource? (the packages are moving data from a D3 database to sql server 2005)

Here is what I have tried so far:

A.
Dim pkgLocation As String
Dim app As Application = New Application()
pkgLocation = "c:Package1.dtsx"
Dim pkg As Package = app.LoadPackage(pkgLocation, Nothing)
Dim myConns As Connections = pkg.Connections

MessageBox.Show(myConns(0).ID.ToString)
Dim myConnMgr As ConnectionManager = myConns(0)
Dim connProperties As DtsProperties = myConnMgr.Properties

connProperties(0).SetValue(myConnMgr, "notsupposed=towork;")
MessageBox.Show(myConnMgr.ConnectionString)

B.
ConMgr = p.Connections.Add("OLEDB")

ConMgr.ConnectionString = "Provider=SQLNCLI.1;Data Source=192.168.0.233; User ID=sa; Initial Catalog=Rmt_New"
ConMgr.Name = "SqlDatabase"
ConMgr.Description = "Sql Connection"

Any help would appreciated.

View 1 Replies View Related

Can't Seem To Run SSIS Packages On The 64 Bit...

Feb 28, 2006

I am connecting to a DB2 mainframe to pull data into SQL 2005. Very simple import. SSIS package works fine on 32 bit. However, once deployed to the 64 bit machine, I get "invalid product license" on the Acquire Connection method.

I've worked with IBM support. I have the correct version of the DB2 Connect client installed. The license is there and in the right place. I can connect to the mainframe from the 64 bit server using the DB2 client tools. I just can't seem to execute the package from Integration Services or run a job in SQL Server that executes the package.

According to BOL, the package should automatically detect the 64 client I installed. It and the 32 bit client I developed with share the same name/id.

Am I missing something? Any hints?

View 3 Replies View Related

Ssis Packages

Feb 7, 2007

Where are SSIS packages stored and how can I see them in SQL Server Management studio?

View 6 Replies View Related