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

SSIS Equivalent To DTS Transform Data Task Properties
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 Replies !

---

DTS: Transform Task, Properties Of Current Row
Hi all,

I have a DTS import that imports up to 50-70 MB of data from about 15 flat text files (CSV), with defined formats.
I have pre-processing that logs an error for missing files, and DTS detects fewer fields than are used in the transformation and we log an error for that.

What I didn't have is a way to detect too many columns in the text file; we will need a good way to detect some bad records with too many comma-separated columns, or if the source export changes the format and adds extra columns in the middle or end, on purpose or accidentally.

I figured out (with help) how to make an ActiveX task to get the source filename, read in a few sample lines, split, and check number of fields, and can run this as a separate step.

However, I'd like to run this in the transform step for this file, and for each row it runs through check how many fields are in current row; if more then log an error (run sql task). If I do separately, would just get a few sample rows at the beginning.
I'd like to make an ActiveX transform in there, not select source/destination field, just have the VB script look at current row.

thanks,
- David

View Replies !

---

Transform Data Task (DTS2000) And SSIS
My first period using SSIS in a real-world application convinced me that there

View Replies !

---

Ho Do I Get The Details From A DTS Transform Data Task Into An SSIS Derived Column Transformation?
i have too many DTS packages to migrate to SSIS, and while examining a DTS package in BIDS (converted with the migration utility) i tried to edit the resulting migrated package, which opened the DTS interface with the two connection icons joined by the big fat arrow with a gear on it...not exactly what i had in mind, iow, it looks like SSIS on the outside, but its still DTS on the inside.
So I stripped out a series of components from a more complex package hoping that simplifying it would reveal the contents of old DTS Transformations tab at least partially set up in a Derived Column transformation.
Can i get there from here, or must i recreate every stinking definition in a derived column manually from the ground up?
thanks very much for your help

View Replies !

---

SSIS Equivalent For Dynamic Properties Global Variable Example
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 Replies !

---

Script Task: How To Compare Files On FTP With Existing Files In Local Folder Before Transfer!
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
 
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
 
Thanks for the help in advance and i apologize for the long lines of code!
 
example for step 1:
----------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo

Public Class ScriptMain

        ' The execution engine calls this method when the task executes.
        ' To access the object model, use the Dts object. Connections, variables, events,
        ' and logging features are available as static members of the Dts class.
        ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        '
        ' To open Code and Text Editor Help, press F1.
        ' To open Object Browser, press Ctrl+Alt+J.

        Public Sub Main()

        Dim cDataFileName As String
        Dim cFileType As String
        Dim cFileFlgVar As String
        WriteVariable("SCFileFlg", False)
        WriteVariable("OOFileFlg", False)
        WriteVariable("INFileFlg", False)
        WriteVariable("IAFileFlg", False)
        WriteVariable("RCFileFlg", False)
        cDataFileName = ReadVariable("DataFileName").ToString
        cFileType = Left(Right(cDataFileName, 4), 2)
        cFileFlgVar = cFileType.ToUpper + "FileFlg"
        WriteVariable(cFileFlgVar, True)
        Dts.TaskResult = Dts.Results.Success
        End Sub
    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
        Try
            Dim vars As Variables
            Dts.VariableDispenser.LockForWrite(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Function ReadVariable(ByVal varName As String) As Object
        Dim result As Object
        Try
            Dim vars As Variables
            Dts.VariableDispenser.LockForRead(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function
End Class

example for step 2:
-------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Try

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

'Set the properties like username & password

cm.Properties("ServerName").SetValue(cm, "ftp.name.com")

cm.Properties("ServerUserName").SetValue(cm, "username")

cm.Properties("ServerPassword").SetValue(cm, "password")

cm.Properties("ServerPort").SetValue(cm, "21")

cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

cm.Properties("Retries").SetValue(cm, "1")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the ftp server

ftp.Connect()

'ftp.SetWorkingDirectory("..")

ftp.SetWorkingDirectory("directoryname")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C: emp", True, True)

' Close the ftp connection

ftp.Close()

 

 

'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

View Replies !

---

New 2005 SSIS Task: File Properties Task
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
 
http://www.pragmaticworks.com/filepropertiestask.htm
 

View Replies !

---

Moving Text File Properties For Sql2k Dts To Sql25k Ssis Packages???
Hi everyone,
 
We€™ve got almost 250 old dts packages which simply loading data into Sql tables from plain files or at the reverse point. Most of them are defined with fixed fields and its fixed positions one after one. We don€™t want to migrate them using Import wizard, on the contrary we€™re producing them from the beggining taking advantatge of SSIS architecture to the full.
And now, we€™re trying to imagine how to migrate automatically that valuable info from Sql Server 2000 to Sql Server 2005 without efforts€¦ You know, any program be able to move that detailed info
to SSIS.
 
So we would avoid to select again all these positions per each file -very tedious and we're lazy

 
I don€™t see how except, of course, migrate them directly
 
Let me know if you need further explanations or more clarity on that.

View Replies !

---

Split The Existing MDF File Into Mutliple Files As A File Group?
I have a huge MDF File - 120 GB File (Had setup as 1 MDF initially) -- Did not anticipate that the DB would grow to that size!!

Anyways.. I heard that the general performance woul grow if i had them as "File Groups"..

Is there anyway - to split the existing MDF file into Mutliple files as a File Group?

Where should i start? Can someone please direct me..

View Replies !

---

Engine Thread Property Of Data Flow Task
Hi guys,
 
The default Engine Thread property of a data flow task is set to 5, is this the best setting? what if I would like to run complex data flow tasks on multi-processor machines, should I increase the engine thread? If so, then what is the recommended Engine Thread number for running complex data flow tasks in a multi processor system?
 
Even if i am running simple data flow tasks on a multi processor machine, should I change the engine thread?
 
Thanks!
Kervy

View Replies !

---

DTS Dynamic Properties Task To SSIS
Hi ,

What is the equivalent of DTS Dynamic Properties task  into SSIS ?

How do I convert this task to SSIS ?

Thanks,

Vikas

View Replies !

---

SSIS Scripts Task - Connection Properties
Hi All,

I am working on a SSIS package which is using a Script task, now I have all the connection properties set up in the .NET script using connection strings, what do I need to do if I have to set this up using a config file or something else which is more secure (I dont want to leave the connection information in the script). Please Advice.

Thanks

 

View Replies !

---

SQL DTS Packages Data Transform Task
Hello, I am working on a module to extract data from a Teradata server to SQL database. I am using a DTS package to extract the data and need to make the data source name (database name and object name) configurable at runtime and to be read from a Config table in the SQL database. What do you suggest is the simplest and most efficient method to do this?

I am trying to use a dynamic SQL query in the data tranform task with the data source as a query. But its giving me a strange syntax error. Can we use a dynamic SQL in the query option of DTS transform task source?

Thanks, Meriya

 

View Replies !

---

How To Transform A Data Task From ODBC
Hi
 
I need to Transform Data from a Transoft Data Source (ODBC) into an OLE DB Connection.
 
The Solution that I Currently have is to Transform using the DTS 2000 Package Task.
 
Is there a way that I can perform this task by using SSIS
 
Regards
Que
 

View Replies !

---

Custom Task With PropertyGrid Control SSIS - Not Able To See Properties In GUI
Hello All Experts,
 
I have created one custom task with PropertyGrid Control and two button on it. I have everything under one class library project.
Problem I am facing is when i load task and clik on Edit I can not see those properties into that GUI and even functionlity of those two buttons (OK and Cancel) not working but I am able to see those properties in default property window.
 
If I create this GUI as a seperate window application then I am able to see those properties in GUI and buttons also working but in SSIS I am not able to load the task.
 
After reading on internet about SSIS they suggest to create everything under one project which I did.
 
Basically I am trying to populate connection managers like Source Connection and Destination Connection when I load this task and there are much more backend functionlity but at first step i m stuck and not able to see those properties in GUI.
 
Please help and give your input on it. I was following "Increment Task" example given by MSDN.
If you need more info let me know.
 
Thanks
 
 

View Replies !

---

Dynamic Properties Task In DTS 2000, Need To Convert It To SSIS
I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

 

Thank you.

View Replies !

---

Cursor, Conditional Split Task, Nested Joins In SSIS
 

Hello
 
Can anybody help me out in
1) implementing cursors in SSIS. I want to process each row at a time from a dataset. I was trying to use Foreachloop container but in vain. Can you please answer in detail.
 
my few other questions are:
1) Can i do nested inner join in SSIS. If yes, how? ( I have three table i need to join Tab1 to table 2 and get join the table 3 to get the respective data)
2) I have a resultsets. I want to split the data according to data in a col.
Say for instance:
Col1 Col2
A        1
A         2
B        3
C        4
C         5
i want to split the data according A, B and C . i.e., if Col1= A then do this, if Col1= B then do this..etc. How can i do this using conditional split task in SSIS
 
 
 

View Replies !

---

DTS Transform Data Task - Specifying Transpormation Manually
Hi Guys,

I'm attempting to specify transformation information manually. I have a SQL source connection and a File Destinaton. All I want to do is specify different column from the same table dynamically. If global variable input is "A" then export columns Col1, Col2 and Col4, if the global variable is "B" then export columns Col1, Col2, Col5, Col7.

Is it possible to do something like

With MyTransformation
.Clear
.SourceColumns.Add(1).Value = "Col1"
.DestinationColumns.Add(1).Value = "Col1"
End With

Thanks in advance...

View Replies !

---

DTS 'Transform Data Task Property' GUI Window
I am importing data from xls file to a db table with a dts. In the time of the dts creation I am using 'Transform Data Task Properties' GUI window to map incoming xls fields (source) to the table columns (destination).
Question: Is there any way to invoke the 'Transform Data Task Property' GUI window in dts runtime and use it to change the mapping dynamically in the run time?
Thanks, Vadim.

View Replies !

---

Transform Data Task Vs Stored Procedure
Hi everybody

I'm still trying to learn the advantage of having stored procedures. I have a DTS that uses a Transform Data Task to append the result of a view into a table. All operations are done locally in the server.

Do I have any advantage if I write a stored procedure to insert the view into the table, and then call the stored procedure in the DTS, in stead of using the Transform Data Task ?


Thanks in advance for your thoughts
ds9

View Replies !

---

DTS Data Transform Task: How To Hide DB And Schema Names?
Hi All,

I'm stuck up with a strange problem.
When i try to setup a Transform Data task in DTS, the table drop down shows fully qualified table name.

i.e. <database name>.<schema name>.<table name>

as you can see in the attached screenshot. With this I cant see the full table name and am not able to make the correct selection.

Where can i change the properties so that it displays only the table name?

Thanks
Rohit

View Replies !

---

SSIS Moving Multiple Files
I have a number of XLS reports in template form.  I want to move these to a new location on the File Server and after they have been populated move them to another location on the File Server.

I have seen some proposed solutions but I haven€™t found any that work.  This should not be difficult and I envisage using a File System Task and a Foreach Loop Container.  However passing the multiple file names to the File System Task errors repeatedly.

Any help would be greatly appreciated!!

Paul Boynton

View Replies !

---

SQL2005 Equivalent Of The Data Import Task In SQL2000
I had to upgrade to SQL2005 when my customer did so. Everything went OK until I had to load additional data to the datamart. The SQL2000 task to load data from flat text files was straight forward and literally took me seconds tyo set up and execute and has never given me problems. I have now spent 3 days straight trying to do the same in SQL2005  going in ever wider circles. I have got SSIS up and going, I saved a dtsx package that won't run without a multitude of errors ranging from truncation errors (which I tried to avoid by meticulously ensuring all field definitions on the import and export side are defined and correct) to errors relating to not being able to connect to the database I am working on or of not having the right security access. I am at my wits end. I need help!

View Replies !

---

Parameter Error When Performing A Transform Data Task From Access To SQL Server 2K
I have an Access 2.0 database that holds call data on a mapped drive. I am running MS SQL Server 2000. I can open it and view the records inside. I can even run the query below and get results, if I removed the CallDate and CallTime parameters.

SELECT CallDate, CallTime, Mid(CallRecordData, 68, 3) AS Extension, 'I' AS Direction, Mid(CallRecordData, 34, 11) AS Called,
Val(Mid(CallRecordData, 18, 2)) + Val(Mid(CallRecordData, 21, 2))/ 60 AS Minutes, Val(Mid(CallRecordData, 21, 2)) AS Seconds
FROM CallRecords
WHERE (CallDate = ?) AND (CallTime >= ?) AND (CallTime < ?) AND (Mid(CallRecordData, 30, 1) <> '9')

When I preview in the Transform Data Task, I get:
Package Error
Error Source: Microsoft JET Database Engine
Error Description: No value given for one or more required parameters.

When I look at the parameters, they are listed. I check their values, and they have the appropriate values (DateCalled, String, 07/14/2005) (StartTime, String, 06:30) (EndTime, String, 07:00)

When I run it in the build query or in Access with a linked table to the source, I can enter the values when asked for them and it works.

Thanks for any help you can provide.

View Replies !

---

Reg:- Assign File Properties To SSIS Varibales(Custom Task) In Property Grid Progrmatically.
HI,

    I need to open a File through File connection manager and want to assign these file properties to SSIS precreated varibale or Newly created varibale. I want to show file properties in Propertygrid. Properties grid will conatin File Propeties Column and SSIS varibale Combobox column. The combo box will contain New variable field. When user select New Variable field, then a new SSIS varibale window will open and we can able create New variable and that Newly created variable should add to that property comboBox.

 

For Instance if we create a new varibale Name "Creationdate" by clicking on New Varible in ComboBox, then that CreationDate variable should add to Property ComboBox in PropertyGrid. After adding when we select that variable Name "Creationdate" then that selected file Creation date should assign to SSIS varibale "Creationdate"  field.

 

Any Comments or sample will help me.

 

Nitin

View Replies !

---

New SSIS, Info About Working With Directories Moving Files Etc
 

Hey,
 
What I am trying to achieve currently with SSIS is to view the content in one folder for example 'New' and if there is files in this then move it to 'Archived'.
 
Any nice liinks or tutorial or general advice you guys could give me ?

View Replies !

---

Conditional Split Transform
 

Good Day All,
I have an interesting situation that I cannot believe is unique. I have a flat file (ragged right) that contains 5 different record types. Each row in the file identifies the record type in the first character. The layout is something like this:
 
File Header
  Group Header (Contains group id number)
    Data Item (Contains group id number)
    .
    .
    .
  Group Footer (DOES NOT CONTAIN GROUP ID NUMBER)
  Group Header (Contains group id number)
    Data Item (Contains group id number)
    .
    .
    .
  Group Footer (DOES NOT CONTAIN GROUP ID NUMBER)
File Footer
 
Now I only want to extract data for ONE of the aforementioned groups, however I need the group footer as well because it contains some control totals for the group. The real problem is that the footers do not contain the group id number it goes with. It is a completely positional thing. Silly, yes I know but this particular file layout is an industry standard.
 
I thought the conditional split would be the way to go. Unfortuately, it seems the conditional split wants to split the entire data set before passing the results down stream rather than processing a single row at a time and passing that row down stream before processing the next one. (Blocking versus streaming I think its called) I could do it in a single god-awful script but I would rather try not to have to code the entire thing.
 
Any suggestions would be very helpful..
 
TIA,
Don
 
 
 

View Replies !

---

Inconsistent SSIS Data Transform Behavior
 

High all,
 
I have a very simple SSIS package that is moving data from a DB2 database to a Teradata box.  I've run it around 10 times, twice it pushed data over, the balance of the time, it executes with no error, but moves nothing over.  In the "incomplete" runs, a command line box pops up for half a second, then the package ends.
 
Does anyone have ideas as to why this behavior is occurring?
 
Thanks,
 
Mark

View Replies !

---

Combine Data And Split Into Separate Txt Files For Each Header/detail Row Groupings
I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
 
1)       Takes an incoming txt file.  Example txt file: http://www.webfound.net/split.txt    
 
The txt file going from top to bottom is sort of grouped like this
     Header Row (designated by €˜HD€™)
          Corresponding Detail Rows for the Header Row
           €¦..
     Next Header Row
          Corresponding Detail Rows
 
     €¦and so on  
 
       http://www.webfound.net/rows.jpg
 
2)       Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table.  A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started.  The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
 
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file.  So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file. 
 
This is where I€™m stuck.  How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
 
The filenames of the txt files will vary and be based on one of the column values already in the header table.
 
Here is a print screen of my package so far:
 
http://www.webfound.net/tasks.jpg
 
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
 
http://www.webfound.net/DataFlow_Task_components.jpg
 
Let me know if you need more info.  Examples of the actual data in the tables are here:
 
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
 
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg

View Replies !

---

SSIS Package, How To Transform Data Type Fields.
Hello,

I have an OLE DB Source and i want to transform the data type fields of the table before i export the table in an OLE DB Destination.
Is there a way to transform numeric value to float, and numeric to nvchar?

Thank you in advance.

View Replies !

---

Logical Equivalent IN Operator In A Conditional Split Transformation
I am using  a Conditional Split Transformation to find incorrect city listings in a specific list of zipcodes.  I have the following condition:
 
LTRIM([PatientCity]) !=  'Wichita Falls' &&   [PatientZip] IN '76301','76302','76305','76306','76307','76308','76309','76310')

 
I found the && which is the logical equivalent to AND.   I now need a logical operator for IN.
 
Any ideas or approaches on how I may do this?
 
 

View Replies !

---

How Do I Propagate Custom Properties To Downstream Transforms In A Data Flow Task
I implemented a custom source adaptor.  I want to be able to associate custom properties with each of the output columns. I want them to be passed downsteam. The idea is to be able to retrieve these information in a downstream custom transformations of ours and process the various columns accordingly. How do I go about doing this?I noticed that the IDTSCustomProperty90 seems to have a local scope only.

View Replies !

---

SSIS Data Mining Model Training Transform (Nested Tables)
I guess this question belongs here rather than Data Mining section..  Maybe... 

I can't figure out how to put nested tables into the Data Mining Model Training Transform (SSIS).  I can do a simple case table, but how do you get those nested tables with DM Training Transformation?   Any ideas?  Samples?

Thanks in advance,

-Young K

View Replies !

---

SSIS Data Mining Model Training Transform (Nested Tables)
I can't figure out how to put nested tables into the Data Mining Model Training Transform (SSIS).  I can do a simple case table, but how do you get those nested tables with DM Training Transformation?   Any ideas?  Samples?

Thanks in advance,

-Young K

 

View Replies !

---

Moving Data Files To A New Disk, Same Server
Hello, I have been having a bit of trouble finding help on the safestway to move data files to a different disk on the same server. Mosthelp is about moving data files to a different sqlserver. I just wantto move the files to a different drive on the same server. Any helpwould be appreciated.Thanks,David

View Replies !

---