Where Is The Forum For DTS -&&> SSIS Migration Questions?
Jan 29, 2008I have a question regarding a DTS (SQL server 2000 package) to SSIS (SQL SERVER 2005 package) migration.
which is the best forum to post my question ?
ADVERTISEMENT
The SQL Svr Migration Assistant (SSMA)..am I In The Wrong Forum?
Jun 18, 2008I have SQL 2000 and installed SQL 2005 Express. Had done some test migrations of
Access 2003 to SQL2000 using the tool for that. Upgraded that migration assistant to
SQL 2005 (my mistake) and now I have 2 instances of SQL2005 running (according to
surface area configuration) and even the server mgr for SQL2000 can see those instances..
but SSMA 2005 refuses to see them.. My migration test project is dead in the water..
Can I get a copy of SSMA for SQL2000 back?..Anybody?..
Thanks..
Migration To A Database Questions
Oct 2, 2007Hello,
I'm new to databases but not to programming in general. I've got a few applications which currently sit on a single PC and read/update a particular XML file on the box. The applications are C# apps and use FileSystemWatchers and a mutex to keep watch for changes and update the XML file as needed. It has worked reasonable well for me.
Now, I need to run these applications on separate machines, so it seemed a good point to consider moving toward a database solution.
I'm hoping that there is a straightforward solution using SQL Server that will ensure that my applications alway have the current view of the world. I had run into instances with my existing setup where once in a while an application thought its DataSet was up to date, but wasn't.
Can someone point me in the right direction to implement the solution? I suspect that this is a really common need for lots of programs that are distributed. I'm still quite a newbie with databases and SQL Server, so I'm not even sure I'm using the right terms to describe things.
Thanks,
Matt
Please Post Setup And Installation Questions In The Setup Forum
Nov 17, 2005This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.
Thanks
Package Configuration/migration Questions
Apr 19, 2007I have a two questions that hopefully someone can assist.
Question 1
We use the XML file option to store configuration information for our SSIS packages. It seems that the XML configuration files are not being used in all instances even though we specify the file in SQL Agent. From what I€™ve seen, properties like server name and SQL user id are being pulled from the XML configuration file. Other types of references in the configuration file like email addresses for a send email task are not being pulled.
The way I determined this is that I manually edited the configuration XML file on SQL Server. Changes done to email addresses in the configuration file are not used when I run the package via SQL Agent. But changes to server name and/or SQL user name are used.
If we run the deployment wizard and update the email addresses via the Wizard, the changes are reflected in the configuration file and the SQL Agent job picks up the changes. It seems a waste to have to rerun the SSIS migration wizard just to update the configuration XML file.
We are using SQL Server 2005 SP 1. When we deploy SSIS packages, we run the deployment wizard and choose the following options:
SQL Server deployment and check the Validate packages after installation.
Specify SQL Server, Use Windows Authentication, and check the Rely on server storage for encryption
Update information in the configuration file within the wizard
Question 2
When we migrate SSIS packages on the production server, we get the following error message during the Package Validate step: The SSIS logging provider has failed to open the log. Error code: 0x80070005. Could this be caused by differences in the SQL directory structure between the Development and Production Servers? On the Development workstation and the Development server, a directory called C:Program FilesMicrosoft SQL Server90DTSLogProviders exists. On our production server, this folder is located on D:Program FilesMicrosoft SQL Server90DTSLogProviders.
Is It Ok To Post A SSIS Contract Vacancy On This Forum?
Jun 30, 2006Hi,
I wonder if anyone could tell me whether it is acceptable to post a SSIS contract vacancy on this forum?
If not, where would be the best forum/site to find UK-based SSIS developer contractors?
Many thanks,
Gavin.
DTS Migration To SSIS
Nov 12, 2007I 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
DTS Migration To SSIS
Jan 10, 2006Good 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.
Migration From DTS To SSIS
Sep 6, 2007Hi 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.
Migration Of DTS To SSIS
Nov 6, 2006Well..
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?
SSIS For Data Migration
Apr 5, 2007Hi 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.
Migration Of DTS Packages To SSIS
Feb 16, 2006I 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
DTS To SSIS Migration Question
Aug 2, 2007Its 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?
SSIS Migration Between Servers
Sep 21, 2007Good 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.
Data Migration Using SSIS In SQL 2005
Aug 8, 2006Greetings,
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
Migration From Sql Server 2000DTS To SSIS
Mar 26, 2008Hi,
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
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
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
SSIS Migration Wizard Question
Mar 18, 2008I applied the DTS Migraiton Wizard to a DTS package and it worked fine with one BIG exception. The DTS package imports a fixed-width record, length about 1500 bytes, with 60+ "parse" lines (one per field). The migrated SSIS package did not preserve any of the parse structure. That's alot of error prone worke to recreate and we have another 3 DTS packages based on the same parsing. Any suggestions on how to preserve the parsing structure in SSIS?
TIA,
barkingdog
Migration From Livelink To Sharepoint Using Ssis
Apr 9, 2008hi
in my department we used livelink for a long time. now we have the order to migrate to sharepoint.
i have the task to make a package in ssis which reads from livelink using the livelink api and transform the data to sharepoint.
i have begun creating a custom connection manager. it is recognized by ssis and i can select it in the new connection window. what do i have to do in the aquire connection methods? do i have to create a custom datareader?
thanks in advance for your help
peter
Migration SSIS In Msdb To Other Server
Aug 7, 2006I depolyed several package in a server A msdb in some logical folders
E.g. msdbTask1package1
msdbTask2package2
msdbTask3package3
I would like to migrate the package to other server B and keep the logical folders tree.
I know I can reploy the packages in B and move them to the logical folders tree one by one, it is too slow.
Is there any fast method to migrate the package and keeping the tree?
Thanks.
SSIS Pkgs Migration Question
May 30, 2008
I have a server that is sql2000. We are planning to upgrade that to SQL SERVER 2005.
We also have a test 2005 server.
Before going live, we are migrating all 100 DTS pkgs that we have
in sql server 200 over to SSSS on the TEST 2005 sql Server. once all THE 100 DTSPKGS THAT we have in SQL
2000 PKGS Are scucessfuly migrated/tested on test 2005 server, only then we will
move forward with migrating the PRODuction 2000 Server to 2005.
My question is:
1. When we are ready to migrate PRODuction 2000 SERVER, TO 2005, We will need to transfer/copy all
the SSIS 2005 PKGS THAT WE HAD tested on the test 2005 server. Once we move that to prod 2005,
we will need to change datasource connection to point to the PRODuction 2005 SERVER.
However my question is WHAT IS THE EASIEST/FASTEST WAY TO tranfer/copy all these (100+ ssis pkgs)
2005 ssis pkgs that are on the TEST SERVER
OVER TO PROD 2005 SERVER? Do we transfer each and every SSIS PKg manually ,or is there any easy way to get
these 100 SSIS Pkgs, over from TEST 2005 TO PROD 2005?
SSIS Questions
Mar 4, 2008Hi all,Can you please share some interview questions that can be expected on SSIS 2005?
View 8 Replies View RelatedSSIS ETL Questions
Mar 23, 2007I am using SAS Dataset and loading in to SQL server 2005.Now i want to delete columns which have 0 values and null values.Can anyone guide me how to do this?thank you.
View 3 Replies View RelatedMigration Issue From DTS 2000 To SSIS SQL 2005
Jul 13, 2007Hi,
Hi,
I am trying to migrate DTS 2000 packages to SQL 2005 SSis using Package Wizard. When I am invoking and after the passing server information of source and destination, while reading the packages it is giving error
TITLE: Microsoft SQL Server
------------------------------
This wizard will close because it encountered the following error:
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)
Could someone can help in this regards. As this is a important task for me and I am stuck on this which has to be resolved by end of today.
Thanks
-VD
Venkatesh
How Can I View The Design Of The Ssis Package After Migration
Jun 8, 2007OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:
servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?
Converting DTS To SSIS - 2 Questions
Apr 11, 2008Hi,
I was wondering if anyone know how to do Dynamic Properties Tasks in SSIS? And, does anyone know how to create a global database in SSIS so I won't have to change all of the database names in my package? I'm sorry, I'm new to SSIS and I'm just really frustrated. Thank you.
SSIS And Security - Questions
Mar 15, 2007Hello,
I realize that I am confused about SSIS and security.
In BIDS, I work on and modify my packages. That part I understand. Then, I want to build my project, then deploy to SQL Server 2005. I know how to do that too (for the most part, please see below):
My confusion arises around the "Protection Level" options in the package properties. Right now I have everything set to "EncryptSensitiveWithUserKey". My understanding is that I need to change this in order to run my packages from SQL Server jobs, because only the creator of the package can currently run the job.
So my question is, since I want to deploy to SQL Server, don't I want to change the "Protection Level" to "ServerStorage"?
However, it will not let me change the protection level to ServerStorage. It says "The protection level, ServerStorage, cannot be used when saving to this destination"... presumably because it is attempting to save to the file system.
However, even when I built out my packages and saved to SQL Server, I could not change the protection level either.
Which is why I am really confused... Once you save your packages to SQL Server, how do you make changes to the packages, so that the changes are reflected in the packages stored on SQL Server?
There is some concept I am not understanding here.
Thanks for any help
Questions On SSIS Tool.
Sep 28, 2007
I have some questions on SSIS tool I tried to read many docs to understand SSIS.
Now I am looking forward for below questions.
How to use SSIS for :
1. How to Export data from SQL Server 2005 in to Access database using SSIS?
2. How to Import data from Access .mdb file to SQL Server 2005 database using SSIS?
3. How to Import Oracle10g data in to SQL Server 2005 databvase using SSIS?
4. How to Import Oracle10g Data to Access file using SSIS?
5. How to Import SQL Server 2000 data in to SQL Server 2005?
It will be great to have step by step explanation.
Thanks in advance.
SSIS Boolean Questions
Oct 24, 2006Please write YES/NO against point number in your answer i.e. for example:
1) YES, 2) YES, 3) NO, 4) YES etc.
We have source SQL Server 200 databases in one instance and target SQL Server 2005 in another instance both being on different windows 2000 server systems.
Target OLAP DB design is derived from source OLTP.
Most master & transaction tables are as is but some target tables are a result of merging source tables.
We need to do SSIS packages based migration using data flow task based on column mapping & queries using Business Intelligence Development Studio (BIDS). There are no transformations required.
1) Can I group packages under project into sub-groups i.e. super packages?
2) Can I execute a set of packages together which are logically/physically grouped?
3) Can we execute set of ordered packages/individual packages from command line and even
stored procedure?
4) Can we maintain transaction integrity across the entire set of ordered packages we execute?
5) Can we send a mail to mail id(s) in case of events like OnError etc.
6) Can we schedule the packages?
7) Does SSIS provide via BIDS a way to compare data in source with target to decide what got
inserted/deleted/updated and based on that do the necessary?
8) Can SSIS packages be developed including code for data encryption?
9) Can we pass parameters to packages that get called from stored procedure?
10) Can we call stored procedure/function in SSIS package?
SSIS Related Questions
Nov 7, 20060) We are migrating data from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
1) We have an data flow task SSIS package.
2) We managed to send a mail with various variables & their values in subject or body of mail.
3) How do we send mail with details of Error/Warning using OnError/OnWarning events?
4) Can we have bad tables i.e where records which failed to migrate get inserted into. If yes, what are the steps to implement this.
5) Can we have a mix of in-built & custom logging. Is logging provider necessary to do logging. What logging provider to use?
6) Where are the errors that come during build, deploy, install and execute stored? Is there a log file(s) & what it their location?
7) Please post links if that is better.
Some Basic SSIS Questions
Jan 18, 2008I have been working with SSIS and find myself stopped on what seem like very basic tasks.
Am I right in thinking that, if you want to import a flat file into a SQL datatable, in most PC based systems, that you have to manually cast EVERY SINGLE COLUMN to unicode string, if you don't want to get the 'cannot convert between unicode and non-unicode string data types'? I mean, this is surely a VERY COMMON task, taking a CR/LF delimited ASCII file and dumping it into a database tables? What if you have 50 fields to import?
Am I the only one who finds it very difficult to cast a string from a textfile to a date format? At this point, I always just dump the date to a 8 character string and save it that way, and use a SQL view to cast it to a date. At least SQL Server can do 'basic date conversions'. Is there any way to freeze execution and find out what data was provided to a derived column, if you're trying to do some substring operations and hodge together a date? I couldn't find any. I usually put a extra column in a datatable and plunk some data there to find out 'what it looked like', how else can you debug an SSIS program?
Is there a planned service pack or update to SSIS that will make this more usable? Does Microsoft ever intend to implement an UPSERT type module? Or a File Move operation that handles directories that fill up with log files, each of which have to be added to a datatable and then moved to an archive directory (surely, another very common task - I figured out how to accomplish this but it is far from intuitive, the way it currently works). Sorry for grumbling, but honestly, I find turnaround time to develop a very simple import program is RIDICULOUS. It's quicker to write a COBOL program to issue a bunch of formatted SQL UPDATE statements than using this application.
SSIS Interview Questions.
Nov 27, 2006Could some of the expert(s) list interview questions that might come up when applying for ETL/SSIS developer I or II positions.
Thanks