DTSDestination - Equivalent In SSIS?

Apr 9, 2007



Hi,



I am converting an ActiveX script. I am looking to replace the functionality of this statement:



If DTSDestination("CartoonCharacter") = "Felix" Then ...



So, it's checking the value in the TABLE, not the file.



I need to rewrite this for SSIS. What is the recommended approach? Is there a built-in task that will do this and how?



Thanks much

View 1 Replies


ADVERTISEMENT

DTSDestination Not Defined !!!!!

Dec 26, 2006

Hi, i'm a beginner in DTS / Activex , in SQL Server 2000.

I need to update 500.000 records of a TableB (containing 2.000.000 rows) with a TableA (containing 500.000 rows)

With Enterprise Manager I made a new DTS package (my first package!)

I define / drag 2 Connections as OLE (the first-right icon)
(but i don't define any Activities as dts. Then i define the following ActiveX (VBs) :


Function Main()

if DTSDestination("NDG") = (DTSSource("NDG")) THEN

DTSDestination("BANCA") = DTSSource("BANCA")
DTSDestination("NDG") = DTSSource("NDG")
'Pao: LA LOGICA DELLA TRASFORMAZIONE
if (DTSSource("PTF")) > 0 THEN
IF (DTSSource("SEGMENTO")) > 0 THEN
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
ELSE
DTSDestination("CAMPO_N1") = DTSDestination("SEGMENTO_CLIENTE")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
END IF
ELSE
IF (DTSSource("SEGMENTO")) > 0 THEN
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = 0
ELSE
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
END IF
END IF
END IF
Main = DTSTransformStat_OK

End Function

+++++++++++

but when I run it I obtain the following error message: DTSDestination not defined, idem for DTSSource.


Please help me, it's IMPORTANT !

Thank You.

View 1 Replies View Related

Varchar(max) Equivalent In SSIS

Apr 4, 2007

Hi All,

I have a column in my table that is of varchar(max) datatype, when I try selecting the same using a ExecuteSQLtask and assign it to a variable of String datatype, its failing with the error,

[Execute SQL Task] Error: Executing the query "Select [Path] from PARAMETERS where [Name] = 'List File'" failed with the following error: "The type of the value being assigned to variable "User::list_file_path" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What is the equivalent datatype, is String not good enough? Is there something I am missing out here ?If someone has gone through this and figured out a solution please help....

View 15 Replies View Related

What Is The Equivalent Of 'SELECT * INTO' In SSIS?

Jul 1, 2005

I have 100+ tables that are imported from several OLE DB sources. In my current solution I am using 'SELECT * INTO' so I can grab everything without having to manually define a schema. I'm trying to build a SSIS package that will perform the same task but running into two problems. Any ideas?

View 7 Replies View Related

Equivalent Of DTSStepExecStatus In SSIS

Jul 17, 2006

Hi,

I am migrating a dts package into SSIS.The dts package has a dynamic property to set the execution status of a data pump task in the package.

the dts paskage is setting the DTSstepexecstatus of the datapumptask to 1 or 3 ie waiting and inactive.

The dynamic property cannot be migrated to SSIS.

May i know any equivalent property in SSIS to DtsStepExecStatus property of Dts.





Thanks in advance

Neethu

View 5 Replies View Related

BEST SSIS Equivalent For DTS2000 CURSORS

Nov 2, 2006

Hi everyone,

Right now we are trying to replace all stored procedures by SSIS logic.

One of the stored procedures does the following,




1. For Every record in PSTREELEAF find the Hierarchy FROM
PSTREENODE and store them in local valriables.
First the stored procedure pulls all the data from the PSTREELEAF table based on join condition into local variables using cursor. Then for every record in the cursor it finds the corresponding records from PSTREENODE table. This is just a part of the stored procedure.
can anyone tell me how can i do this in SSIS? and also tell me which task would be a perfect replacement for CURSORS in SSIS?

Thanks,
Praveen

View 2 Replies View Related

Use An Equivalent Of ROW_NUMBER() In SSIS Package

Jan 4, 2007

Hi,

I would like to use an equivalent of ROW_NUMBER() T-SQL function in a SSIS package.

I could create a temporary table and use a SQL request with ROW_NUMBER() function but I would like to avoid that and generate this column in the dataflow.

Is there an easy way to do that?

Thanks,

Arnaud Gervais.

View 12 Replies View Related

SSIS Equivalent For Dynamic Properties Global Variable Example

Apr 17, 2007

Hi,



I have a dts package that currently uses a dynamic properties task to set the values of global variables. Each variable is based on the value of a query to the database.

I am in the process of migrating this dts package to SSIS but cannot find an equivalent function. I have looked at property expressions but cannot get this working the same way.



Any help would be appreciated.



Thanks

Lyn

View 11 Replies View Related

SSIS Equivalent To DTS Transform Data Task Properties

May 3, 2007

I am trying to read in a flat file, transform the fields and store into a destination database.



In DTS, this works using Transform Data Task Properties. I define the columns and then have a VB script on the Transformations tab that changes any bad data.



Is there a way to do this in SSIS that I can define the column transformations and re-use my VB scripts?



Linda

View 16 Replies View Related

Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)

May 7, 2007

Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.



In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.

Linda



Here is the old VBScript Code:

Public Sub Main()

Option Explicit

Function Main()

Dim MovementDataDir

Dim MovementArchiveDataDir

Dim MovementDataFile

Dim MovementArchiveDataFile

Dim FileNameRoot

Dim FileNameExtension, DecimalLocation

Dim CurMonth, CurDay

Dim FileApplicationDate

Dim fso ' File System Object

Dim folder

Dim FileCollection

Dim MovementFile

'======================================================================

'Create text strings of today's date to be appended to the archived file.

FileApplicationDate = Now

CurMonth = Month(FileApplicationDate)

CurDay = Day(FileApplicationDate)

If Len(CurMonth) = 1 Then

CurMonth = "0" & CurMonth

End If

If Len(CurDay) = 1 Then

CurDay = "0" & CurDay

End If

FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)

'=====================================================================

' Set the movement data directory from the global variable.

MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value

MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value

fso = CreateObject("Scripting.FileSystemObject")

folder = fso.GetFolder(MovementDataDir)

FileCollection = folder.Files

' Loop through all files in the data directory.

For Each MovementFile In FileCollection

' Get the full path name of the current data file.

MovementDataFile = MovementDataDir & "" & MovementFile.Name

' Get the full path name of the archive data file.

MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name

DecimalLocation = InStr(1, MovementArchiveDataFile, ".")

FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)

FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)

MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension

If (fso.FileExists(MovementDataFile)) Then

fso.CopyFile(MovementDataFile, MovementArchiveDataFile)

' If the archive file was coppied, then delete the old copy.

If (fso.FileExists(MovementArchiveDataFile)) Then

fso.DeleteFile(MovementDataFile)

End If

End If

Next

fso = Nothing

folder = Nothing

FileCollection = Nothing

Main = DTSTaskExecResult_Success

End Function

View 6 Replies View Related

SQL Equivalent Of MAX And IIF

Nov 23, 2005

I have looked around and found the equivalent for IIF (Access) to be aSELECT CASE in SQL. I have tried this with no success. I am also lookingfor the equivalent of MAX and have had no luck. The portion of thestring I am trying to SQL'ize is:SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN FROM ADCNINNER JOIN Sheet ON ADCN.RecordID = Sheet.RecordID WHERE (Sheet.Drawing= '" & x & "') AND (Sheet.SheetNumber = 0);This portion is the most important:SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN*** Sent via Developersdex http://www.developersdex.com ***

View 3 Replies View Related

SQL Sourcesafe Equivalent?

Aug 15, 2006

Recently, my company's deveolpment machine crashed. We had to format the harddrive which meant we lost some of our databases.
We managed to restore the databases, but we do not know in what state, i.e. which stored procedures were written between the restore point and the point the machine crashed.
Tow questions related to this:
1. I was wondering if there is any product that is similar to soursesafe but appied to databases, so that this problem will not happen again. Does Enterprise manager do something like this?
2. I was also wondering if there was anyway to undo an sql command. e.g. my colleague recently ran a command to update a database, but forgot to write the where clause!! That data was recovered aswell, but it would be nice to have some sort of undo button.
Thanks in advance.
Jagdip

View 1 Replies View Related

Reverse Equivalent To TOP

Dec 28, 2006

Is there anyway I can return only the last row of a query, like TOP does for the top most items?I would like to return something like this:SELECT BOTTOM 1 Column_CFROM Table1WHERE Column_A = somethingAny help would be greatly appreciated.  Thank you!  

View 3 Replies View Related

MS SQL Equivalent Of Substring_index

Jan 7, 2008

Hi everyoneI have a table 'users' with column 'residence' New York, NY Houston, TXSan Antonio, TX In MySQL I can select the city by running the following command SELECT SUBSTRING_INDEX(residence, ',' , 1) FROM users.  How would I accomplish the same thing using ms sql?  Thanks in advance 

View 2 Replies View Related

Rs.getrows Equivalent

Jul 10, 2004

Hi

Is there and equivalent of rs.getrows in asp.net? I want to fill an array quickly from a datareader?

ta
Lbob

View 2 Replies View Related

Equivalent To Autonumber

Jun 20, 2001

What is the equivalent to autonumber in SQL 7? I ported over some data into SQL but when I bring it up in Access 97 as linked tables it shows that
what used to be autonumber in Access is listed only as number in SQL. So what is the equivalent to Autonumber in SQL?

View 1 Replies View Related

Equivalent To ON UPDATE In SQL

May 6, 2008

!#perl

How do i structure my script to do the same thing in SQLExpress as in MySQL. My MySQL statement goes like:

ALTER TABLE groups ADD Last_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER Report_List

SQL does not recognize the ON UPDATE part, but i want the same function out of that collumn.

Any help here is greatly appreciated,
??
JOhn

View 11 Replies View Related

Sql Syntax Equivalent

Mar 12, 2007

Hello,

In this sql syntax
Quote: "SELECT table1.ID, table1.Catagory, table1.PostCount, table1.LastPostDate, table1.Description, table2.Username, table2.ID AS UserID FROM Catagories AS table1 INNER JOIN Users AS table2 ON table1.LastPostBy = table2.ID"

Is this the equivalent?

Quote: “SELECT table1.ID, table1.Catagory, table1.PostCount, table1.LastPostDate, table1.Description, table2Username, table2ID AS UserID FROM Categories AS table, Users AS table 2 WHERE table1.LastPostby = table2.ID”

View 7 Replies View Related

INDEXTYPE Equivalent ?

Jun 16, 2004

Hi,

There is a concept called INDEXTYPE in Oracle, Is there any equivallent for the same in SQL Server ?

Oracle example :

CREATE INDEX index1 ON test_table(col1) INDEXTYPE IS CTXSYS.CONTEXT

What is the SQL Server equivalent query for the above ?

Thanks,
Sam

View 1 Replies View Related

Set Autocommit Equivalent ?

Jul 8, 2004

Hi,

What is the SQL Server equivalent for "set autocommit on" of Oracle ?

Thanks,
Sam

View 4 Replies View Related

SQL Server Equivalent

Jul 9, 2004

I have 2 SQL statements in Visual Basic(with sybase as backend)
1) "set option DBA.MAX_STATEMENT_COUNT = 1069999900"
2) "set option DBA.MAX_CURSOR_COUNT = 1069999900"

And when I migrated the DB from Sybase to SQL server and try to run the vb code it is giving me error in that SQL statement as MS SQL server might not be recognising the above two statements. Is there an equivalent of this in SQL server.
Thanks

View 1 Replies View Related

Rownum Equivalent ?

Aug 16, 2004

Hi,

Rownum returns the serial number for the records in Oracle.
Id there an equivalent for the same in SQL Server ?

select rownum from test_table;

Please advise,

Thanks
Sam

View 1 Replies View Related

NOCACHE Equivalent ?

Oct 27, 2004

Oracle query :

create table test(sno int, sno1 int) NOCACHE

Is there any equivalent in SQL Server for the above (specifically for the NOCAHE syntax)

Please advice,

Thanks,
Sam

View 2 Replies View Related

Is There An Equivalent Syntax To TOP IN SQL?

May 16, 2006

Hi everyone, I am new to SQL, and would really appreciate help with this.

I have a database with the following fields:
IDNumber: sequential running from 1 to approx 50000
SURNAME: Surname
FNAME: Forename.

I want to return the last 100 IDNUmbers and return the surname and fname associated with the IDNumbers.
When I try TOP it gives me IDNumbers 1 to 100, is there an equilvant for the bottom 100 numbers.
Please help if you can.
Thanks
Scott

View 2 Replies View Related

Is There A C# Equivalent To Sysem_user?

Apr 11, 2008

is there a C# equivalent to system_user?

I asked my dev team but they all work from home on Fridays (read sleeping on their couches), and I have not heard back yet.

View 3 Replies View Related

Does MS SQL Have An Equivalent Function...?

Nov 16, 2006

...For Oracles DECODE function?

I am trying to get a conditional output RETURNed to the Grid output and have not found it in SQL Help. Help!!!

Here is Oracles example:

Select Distinct City,
DECODE (City, 'Cincinnati', 'Queen City', 'New York', 'Big Apple', 'Chicago',
'City of Broad Shoulders', City) AS Nickname
From Cities;

View 4 Replies View Related

SQLDMO Equivalent

Dec 18, 2006

jess writes "Hi,

IS there a component/way of retrieving a list of SQL Servers or databases on your local network for SQL 2005/Express. The same way sqldmo worked for a .net project.

thanks"

View 1 Replies View Related

To_char Sql Equivalent

Feb 15, 2007

Hello,

What is the sql equivalent of to_char?

Here is a line of code I am trying to fix.


ELSE to_char(COMPLET, 'DD-MM-YYYY') end)COMPLETE, DESCRIPTION, NAME, ADDRESS, JOB, TYP, NUM, OPR, sum(QTYW) QTY, sum(NCommissionAUT) CommissionAUT


Is there anything else in this line of code that I might need to change?

Thanks,

Kurt

View 6 Replies View Related

IIF Statement Equivalent

Feb 22, 2008

Hello,

I'm creating a view in SQL and am looking for something equivalent to the IIF ststement in Access.

Basically I want to create 2 new columns, which are based on the result of 1 of the other columns.

So if in my columns I have :

ID - NAME - FLAG - TIMEFROM - TIMETO
11 - Fred - 0 - 09:00 - 10:30
12 - John - 1 - 11:30 - 15:30
etc, etc, etc

I want to add the first new column saying something along the lines of "If Flag = 0 then DateTo - DateFrom, otherwise Null"

and then add a second column saying "If Flag = 1 then DateTo - DateFrom, otherwise Null"

Therefore the above would show :

ID - NAME - FLAG - TIMEFROM - TIMETO - NEWCOL1 - NEWCOL2
11 - Fred - 0 - 10:00 - 10:30 - 0:30 - Null
12 - John - 1 - 11:30 - 15:30 - Null - 4:00
etc, etc, etc

This in turn would give me 2 new columns which would show value 1 and value 2 - 1 being where the flag is not checked, and 2 being where the flag is checked.

Does this make sense ?

View 8 Replies View Related

Ms Sql Equivalent Of This Oracle

Feb 4, 2006

Hi.I'm a casual sql user. I have found a situation where I need to convert anoracle statement to tsql, one I can just fire off in any sql tool against anms sql server database.I studied the exists statement and I think I understand it somewhat, however Iwas not sure how to get it quite right. If you have an idea and a minute ortwo I'd appreciate any insight or tutorial.insert into authorization (program, optiontitle, usergroup, authorizationid)select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextvalfrom allgroups where exists (select * from authorizationwhere authorization.USERGROUP = allgroups.USERGROUP andauthorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')I believe that because in my data, three values of usergroup from allgroupsreturn true from the exists, that this is supposed to insert three rows intoauthorization.But I can't figure out what to do about the authorization.nextval.. I triedvarious max(authorization)+1etc but nothing seemed to compile/workthanksJeff Kish

View 6 Replies View Related

Right Padding Equivalent

Jun 28, 2006

Hi everyone,Please excuse me if this has been asked before or sounds a bit dim.This is a question asked on another forum but the solutions beingoffered are focussing on programming rather than letting the DB serverdo the work, which I'm not sure is the most efficient solution.However, my confession is I dont use SQL server so can't help themdirectly with the syntax. Hopefully you can help me help them and learna little about SQL Server in the process.Trying to right pad a first name field so the padded string is a totalof 30 chars. It will be output concatenated with the last name field,and each field separated with a "|". So that when output it readssomething like:fname | mylastnameSyntax given was:select id,substring((last_name+','+rtrim(' '+isnull(level,'))+''+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_namefrom studentIssue: It appears this is padding correctly but the spaces are notrendering in the browser. (I have no way to check this as I don't usesqlserver. However, I can understand that multiple spaces are not goingto render in the client browser, if indeed the query is padding withspaces.Question: Instead of using space(), can replicate() be used and aunicode space representation rather than an actual space be used? Or,is there a better way that will ensurethe padding shows in browser?I guess a fixed width font would also need to be used otherwise the30-char blocks could wind up being different widths, which would defeatthe purpose.If there is something I've missed, or you have any suggestions, I'mkeen to learn.TYhanks in advance,Lossed

View 13 Replies View Related

Equivalent To SQL Anywhere GET_IDENTITY?

Nov 27, 2006

Is there an equivalent in mssql to SQL Anywhere's GET_IDENTITY whichreserves the next autoinc value for a table? Yes I know about@@Identity and SCOPE_IDENTITY. I need to get the next autoinc value_before_ I insert the record due to the way the existing applicationworks.I've seen DBCC CHECKIDENT mentioned but that seems kludgy to me.TIA,Jim

View 13 Replies View Related

Equivalent Of Bulkcopy In CF?

Dec 8, 2007

Hi Folks,

Is there any effcent way of copy bulk records from SQL2000 to SDF ? The approach I am using at moment is manually concatenate insert CommandText based on the value datatable from SQL 2000. However, it's not very effcient and I am getting timeout on a table that has around 30 fields and 3,600 records.

Can any one think a better way of doing bulk insert on SDF? Both schema on SQL 2000 and SDF are exactly the same.

Thanks

View 8 Replies View Related







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