Problem Migrating DTS To SSIS. Creating Loop Dinamically

Mar 3, 2008



I'm migrating DTS containing a Loop that performs tasks again:


in the last task:

stpBegin = Pkg.Steps("DTSStep_DTSActiveScriptTask_1");
stpBegin.ExecutionStatus = DTSStepExecStat_Waiting;



My needs are:
1: exec SQL Task 1
2: Script task 1 (write log/insert into table)
3: exec SQL Task 2
4: script task 1 again
5: exec SQL Task 3
6: script task 1 again
etc... etc...

I've one only task for write loginsert into table. Can I switch it to "waiting" status and do it next step?

How can I do this?

View 3 Replies


ADVERTISEMENT

Creating A LOOP

Oct 27, 1999

hi there,

I need to create a loop in a stored procedure to sent mail to all people in the record set,HOW ????

tanx

View 1 Replies View Related

Creating A Loop

Nov 9, 2004

Hi

I need to create an SQL table and automatically populate it with 100,000 records (just one column).

How can I achieve this? The create table part is straight forward enough but how can I get all those rows in there using a single script?

I imagine the statement will require Loop and While.

This is for testing purposes.

View 5 Replies View Related

Creating Object Using Array In A Loop

Apr 17, 2008

I would like to populate an array with an object. In other words, I would like for each element in the array to consist of an object that has several elements. Below is some code so you can see what I'm talking about:
Function buildObj()   Dim countLimit As Integer = 5   Dim mArray() As myObject     While reader.Read()          For counter = 0 To countLimit - 1                mArray(counter).objVal1 = reader.GetValue(0)  'Error is here                mArray(counter).objVal2 = reader.GetValue(1)                mArray(counter).objVal3 = reader.GetValue(2)                counter += 1          Next     End While
     Return mArray
End Function
Dim showValArr As Array = buildObj()Response.Write(showValArr(0).objVal1.ToString)
When I build my site, I get the following error:Object reference not set to an instance of an object.
It's pointing to the line that says "Error is here". Any ideas would be very helpful.Thank you!

View 4 Replies View Related

Migrating SQL 2K DTS To SSIS

Feb 7, 2008

I have lots of DTS packages and jobs in SQL 2K, we are planning to migrate to SQL 2005. Will my DTS and jobs still work? If not, what are the procedures for migration. thanks

View 3 Replies View Related

Migrating DTS To SSIS

Jan 31, 2008



I am tasked with migrating a few DTS packages to SSIS from 32bit 2000 box to 32 bit 2k5 box. These DTS packages contain simple processing of AS cubes. When I run the wizard to migrate the package on my sql 2k5 box, I get the following error during processing:

Could not load file or assembly "Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

and the wizard shows the status being "Stopped". I found the Microsoft.SqlServer.Exec80PackageTask.dll on a 64 bit server located in C:Program Files (x86)Microsoft SQL Server90DTSTasks and copied it to the 32 bit 2k5 box. This still did not work and the error log produces
...........
LogID=4
#Time=9:53 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message= at Microsoft.SqlServer.Dts.MigrationWizard.TasksMigrationModules.CustomTaskMigrationModule.Migrate(Task task, Package& yukonPackage)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)
...........
LogID=6
#Time=9:53 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.MigrationWizard.HelperUtility.DTSMWException: Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at Microsoft.SqlServer.Dts.MigrationWizard.TasksMigrationModules.CustomTaskMigrationModule.Migrate(Task task, Package& yukonPackage)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLMSoftwareMicrosoftFusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLMSoftwareMicrosoftFusion!EnableLog].

I also tried re-installing the backward compatability components to no avail. Any ideas?

View 13 Replies View Related

Migrating DTS TO SSIS

Aug 17, 2007

Hi ,

Getting error even remove Text (Source) task in DTS.
Pl. help me .
Thanks,
Raja

Error:


LogID=23
#Time=11:32 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:PONE_SSIS_PACKAGESPONE_SSIS_SELLTHRUPONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".
---> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:PONE_SSIS_PACKAGESPONE_SSIS_SELLTHRUPONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)

View 9 Replies View Related

Transact SQL :: Creating Stored Procedure With Cursor Loop

Sep 18, 2015

I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten.  Below is the stored procedure I've created:

ALTER PROCEDURE [dbo].[ap_CalcGrade] 
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,

[Code] ....

And below is the "test query" I'm using: 

--  *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
        @Numeric   int,        -- Numeric Grade
        @Result    int         -- Procedure Status (0 = OK) 
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3, 

[Code] ....

This is resulting in an output of: 

A+ 97
A+ 97
C- 72

but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
 
A+ 97
No Find
C- 72

I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it.  Below is the assignment requirements:

Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:

1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade

View 6 Replies View Related

Migrating Cognos DM ETL To SSIS?

May 29, 2008

Hi all - just wondering if anyone could give me an idea of how arduous this task would be (migrating Cognos Data Manager scripts to SSIS). I realize it wont be a trivial endeavour, but wondered how much of a headstart having the initial validated Cognos catalog would be to this project (or would it be pretty much useless?)

Thanks

View 4 Replies View Related

Migrating SSIS Packages

Oct 3, 2007

I have created a SSIS package to load data into a destination table.

Now I cheked that package into main server. Then I tried to change path for both source and destination connection managers. I'm not sure whether I can do that.
But now what I want is load data into a data base which is in another server (Perforce).

Can I do this? Or Am I doing smthing wrong?
If so, Which is the correct way?

View 5 Replies View Related

Help Help : Migrating DTS (Lot Of VB Script) To SSIS:

Nov 29, 2007

I have migrated DTS to SSIS through migration utility. The origional DTS has lots and lots of VbScript.
SSIS has converted it into 8 to 10 Activex script task. When I run the package it gives error in the first activex task.
I found on net the SSIS does not understand Vbcript and this needs to convert into .Net.
Please guide.

View 1 Replies View Related

Migrating Documents In SSIS

May 27, 2008



Hi all,
I need to migrate some documents(in GB's) from FTP Server1 with meta data information in SQLdbA to FTP Server2 with meta data information in SQLdbB. How can we achieve this?

Am new to this concept and got information that we can use FTP task. But unable to proceed how to achieve this. Please help me.

View 2 Replies View Related

Migrating SS2000 DTS To SS2005 SSIS

Mar 11, 2008



Hello,

Is it possbile to migrate SS2000 DTS packages to SS2005 SSIS involving databases with compatibility mode set to 80. If yes, what are the potential issues, which may occur?

Sajish

View 6 Replies View Related

Migrating From Paradox Into Sql Server Using SSIS

Oct 10, 2007



Hi All,

Can anyone suggest me a way to migrate data from paradox tables to sql server tables using SSIS.
Paradox tables are having millions of rows.

Any help on this is greatly appreciated.

Thanks,
SVGP

View 8 Replies View Related

Migrating DTS Active X/ADO Script To SSIS

Aug 1, 2006

Hi All,

In SQL Server 2000 DTS, I had an Active X script that, using ADO, performed the following basic operation:

Retrieve a list of account numbers through Recordset1
While Not Recordset1.eof
Build a sql statement based on fields from current row of Recordset1
Open Recordset2 based on the sql statement built
Run an update query based on results from recordset 2
Loop

I want to bring this over to SSIS, but i'd like to take advantage of the most current "ways of doing things" instead of just using SSIS's activex script.

Does anyone have any recommendations on the best way to handle this type of thing in SSIS? A script task? A script component? I've never really worked w/ ADO.net so if it involves that I'd have to pick up some pointers... any suggestions?

Thanks in advance
Jeff

View 1 Replies View Related

Migrating SSIS Package To 64-bit Server

Apr 6, 2006

Hi,

I have created a simple SSIS package in a 32-bit server and I'm able to execute it using dtexec command in the same server.I copied this package to the 64-bit machine and when try to execute the pacakge using dtexec /f <package path> ,it is giving me the following error

"The connection manager "10.101.24.230.master 1" will not acquire
a connection because the package OfflineMode property is TRUE. When the Offline
Mode is TRUE, connections cannot be acquired."
But in SSIS designer OfflineMode property for the package is by default set only to false but still I get this error in 64-bit.
Any help in this regard would be appreciated.
Thanks
SG



View 1 Replies View Related

Activex Script Not Working After Migrating To SSIS

Jan 3, 2008

Hi,
In my script 'm using
Set oPkg = DTSGlobalVariables.Parent
Set oBeginStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_1")

I guess becoz of using DTSGlobalVariables.Parent in activex,the script is not working.
Can anyone please suggest a solution to run it in SSIS.


Thanks in Advance

View 3 Replies View Related

Migrating SSIS Packages To Production And Job Scheduling

Apr 10, 2006

I have migrated several (7) DTS packages to SSIS in a development environment. In this environment these packages all work as designed. I am now in the process of placing the .dtsx files on the production server and scheduling jobs around them.

I am encountering problems in 3 of these 7 packages. I get the following error:


Message
The job failed. The Job was invoked by User <domainuser>. The last step to run was step 1 (Create and Transfer Credentialling Data).
This is not what I would call very informative. I have opened the dtsx files in question in BIDS on the production server and each has the following error reported:

Error loading <name>dtsx; Failed to decrypt protected XML node "PassWord" with error 0x8009000B" Key not valid for use in specified state." You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
I have looked at the package setting "Protection Level" and all seven packages have the same setting: EncryptSensisitveWithUserKey
Each of the connections within the packages have been defined to utilize Windows Authentictation. And the jobs are set to run under the SQL Agent Account.
What I find most puzzeling is that each of these packages are defined exactly the same and some work correctly and others do not.
Thanks for taking the time help me with this issue.

View 5 Replies View Related

Migrating From SQLServer2000 To SQLServer2005 SSIS Solution

Mar 3, 2006

Hi,

We need to migrate data from Sqlserver2000 database to Sqlserver2005. The SQLserver2000 DB was poorly Normalized and the DB was redesigned inSqlserver 2005. Both the databases are operational.Currently we have around 90 tables that need to migrate the data from the OLD DB to NEW DB, all the table mappings between the old schema and new schema have been successfully established.

Each Table is treated on its own merit and applying its own business rules which allows the data to be transformed to the new tables.The client wants us to build a SSIS solution that needs to performs the following

a. Import all Look Tables.

b. Import all master Tables ( One time import of some tables data and other tables may need to import every 4 hrs)

c. Import all Transaction tables( One time import and other tables may need to import every 4 hrs of data)



My Question is how to apporoach this scenario of pulling one time lookup, master and transaction tables and scheduling 4 hrs data importing process, how to build a SSIS solution for this type of scenario.

Thanks

Jayadev



View 4 Replies View Related

Migrating SQL 2000 DTS Package To SSIS 2005

May 13, 2008



I have successfully used migrate wizard to migrate DTS pacakge to TrainingDTS.dtsx.
What should I do next? when I run

C:>dtexec /file "C:TrainingDTS.dtsx"


Error: 2008-05-13 09:14:31.36
Code: 0xC0029172
Source: File Transfer Protocol Task undefined FTP Task
Description: The connection is empty. Verify that a valid FTP connection is p
rovided.
End Error
Error: 2008-05-13 09:14:31.36
Code: 0xC0024107
Source: File Transfer Protocol Task undefined
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).



View 2 Replies View Related

Help On Migrating From 2000 To 2005- SSIS Package

Apr 4, 2006

Hi,

I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,

- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables

Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow

-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )

-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client


pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not


Thanks for ur help
Niru

View 2 Replies View Related

Migrating Scheduled SQL 2000 DTS Packages To SSIS 2005

Feb 13, 2008

Below is a migration plan that I've compiled to migrate SQL 2000 DTS packages to SSIS 2005. Once these DTS packages have been migrated i will need to create a job and schedule them in SQL 2005.

I would appreciate and feedback or questions on this migration plan.

Migration DTS 2000 packages to SSIS 2005:

1. Will need to save the current production DTS package as structure storage file. We do not have a UDL file. We set the data connections within each DTS package.
2. Go to Sql 2005 - ManagementLegacyData Transformation Services - right-click and open previous saved structure storage file.
3. Modify the DTS data creditentials to reflect the SQL 2005 connection data. Modify any SQL 2000 MAPI settings to utilize SQL 2005 new database mail. Save the package on SQL 2005.
4. After the modified DTS package has been updated and saved on SQL 2005, save this file as a structure storage file.
5. go to BIDS. Create a new SSIS project. Right-click on SSIS packages and select Migrate DTS 2000 package. This will migrate over the DTS 2000 package with the updated SQL 2005 data creditentails.
6. click on the package properties - protectionlevel and change it to dontsavesensitive.
7. right-click and select package configurations..., select to store data creditentials in xml format.
8. right-click on execute DTS 2000 package task, select Edit... and click on Load DTS2000 package internally. This will embed this task into the new SSIS package. Test the package. Continue if successful.
9. Use SSIS deployment functionality to move the package over to SQL 2005 Integration Services. Right-click on package and select Run Package, if successful, create a job and schedule it to run on SQL 2005 Agent.
10. When creating the Job under SQL Agent, change the Owner: of the job to reflect the owner of the new SSIS package. Schedule the job.


I've just coming up to speed on SSIS 2005. Therefore, this is what i've been able to piece together up to this point and I'm looking for some industry advice/feedback on whether or not this is a good migration plan. I need to provide a migration plan to management by 2/18. Thanks

View 1 Replies View Related

Migrating DTS 2000 To SSIS ; Modifying OLE DB Connection Properties

Oct 29, 2007

Hi - Im migrating packages in dts 2000 to SSIS 2005 using package migrator wizard. It loads a CSV to a table in DB. I need to change the server and file location paths after migration. While changing this, im encountering error - "the acquireconnection method call to connectionmanager failed

View 1 Replies View Related

Migrating SSIS Package Using Excel Interop To Server

Apr 28, 2008

I applied the following info and created an SSIS package that allows COM control of Excel:

unable to add reference to Microsoft Excel in VSA

The package runs locally, and can be executed successfully after it is migrated to the server, but both of these processes use local memory space (I think). When I try to run the package via a job, it fails with the following error:

-------------------------------------------------------------------------------------------

Date 4/28/2008 10:34:04 AM

Log Job History (J_MSR_UHC_PC)

Step ID 1

Server SFA30229SQL003

Job Name J_MSR_UHC_PC

Step Name S_MSR_UHC_PC

Duration 00:00:05

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Executed as user: ALEREsql_admin. ...on 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:34:04 AM Error: 2008-04-28 10:34:06.73 Code: 0xC001405F Source: Description: Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information. End Error Progress: 2008-04-28 10:34:09.34 Source: Data Flow Task Validating: 0% complete End Progress Error: 2008-04-28 10:34:09.45 Code: 0xC0202009 Source: msr_uhc_pc Connection manager "Excel Connection Manager" Description: 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". End Error Error: 2008-04-28 10:34:09.45 Code: 0xC020801C Source: Data Flow Task... The package execution fa... The step failed.

----------------------------------------------------------------------------------------------

Microsoft.Office.Interop.Excel.dll has been added to the assembly directory.

Any ideas would be appreciated.

TruckeeBill

View 4 Replies View Related

SQL 2012 :: SSIS For Each Loop

Sep 10, 2014

I have just set up an SSIS package similar to this URL....I have 5 connections in a SQL table which i want to loop through, set each string to be a connection string in the package (variable ConnString) and run a select to pull back information and store in a central table.

The package runs successfully and connects to 5 instances, but it connects to the same instance each time, my local one which is set as the value for variable used for the connection string. How is the Connection string supposed to get passed from the @SourceList variable to the @Connstring variable? Its clear what the problem is, as the results of my query as step one which pulls back all 5 strings just isnt getting populated from @SourceList into @Connstring one by one.

SSIS package =
1) SQL Task - Select connection strings from SQLTable and store results in @sourcelist
2) for each loop - ADO Enumerator, rows in first table @Sourcelist
3) data flow task within the loop - OLE DB Source, dynamic connection as connection manager, running the query i want from a variable, with the destination set to my central database.

View 3 Replies View Related

For Each Loop Container In SSIS

Mar 12, 2008



Hi,

I am using For each loop container in my SSIS package which will loop through more than one input file in the input folder.
All the input files in the input folder are picked by one by one. All the input files in the input folder are executed with the same ExecutionInstanceGUID.

Now, my requirement is to get a new ExecutionInstanceGUID during the execution of each input file in the For each loop container. My package only contains the For each loop container with some logic inside that.

Please let me know if any one can help me for the same.

Regards,
Sriram.

View 6 Replies View Related

Exit For Loop In SSIS

Jan 24, 2008



Hi All,

I have a SSIS package which contains For Loop Container. when a task in the for loop container fails iam setting loop value to 0, even though iam not able to exit the loop. can some help me

Note: when ever the a task in the Loop fails the loop should stop and package should stop.

View 3 Replies View Related

For Loop Container In SSIS

Aug 13, 2007



WHAT IS THE USE OF THE FOR LOOP CONTINER

PLEASE GIVE ME THE EXAMPLE

REGARDS
KOTI

View 5 Replies View Related

Foreach Loop Container In SSIS

May 24, 2006

Could someone send me any links or information on how to loop through an ADO.NET dataset in SSIS? I need step by step information please.



Thank you,

Shiva

View 32 Replies View Related

Can I Loop Through Table And Run Reports From SSIS

May 1, 2007

I would like to loop through a SQL Server table that contains the paths to all the reports we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this?

View 9 Replies View Related

SSIS Package To Loop Through All Servers

May 23, 2008



Hello all,

I currently started working on SSIS. Here is my first assignment; I am trying to get info from all servers. I was able to do it from one server, how can i extend this package so that my package touchs every server and get info from them.

Thanks

View 3 Replies View Related

SSIS Loop Parallell Execution

Apr 24, 2007

Hello,



I have a resultset, which I pass to a loop container, to use as parameters in an ExecuteSQL task for each row.



However, I would like to do some parallell processing, rather then iterate through them sequentially. How can I achieve this? Is there a way I can make the loop iterate before sql task is finished, or should I be using soemthing else entirely?



I hope this makes sense.



Can anyone point me in the right direction please?

View 4 Replies View Related

SSIS: For Each Loop: Destination Error

Feb 19, 2007

Hey Guys.

I am new to using SSIS and need some assistance. I am trying to create a package that will loop through a folder of DBF files and import them into SQL server. Here is what I did so far:
- Created a Foreach Loop Container with a Data Flow Task within
- Created a string variable to store the file names
- Created the OLE DB Source and Destinations
- Created the connection manager with dBASE III Extended Property pointed to the folder where the files are.

When I try to set the OLE Source to use the file name variable, I get an error stating that there is no Destination table defined. I get the same error when I try to setup the OLE Destination. How do I resolve this issue? I want it to import each file into its own table. Is there any way to reuse this file name variable as the destination table name?

Any help would be greatly appreciated.

Thanks!

-Michael

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved