Home » SQL Server

SSIS data extraction question?

I am trying to load data from a flat file. Here is some sample data:

100 John Science 75
100 John Biology 70
100 John Math     80

101 Dave Science 75
101 Dave Biology 70
101 Dave Math     80

I need to put this into a table called 'students' whose structure is: studentid, studentname, science, biology, math

is there anything in SSIS that I can leverage and get this done in an easier and efficient way - any input or help would be appreciated?



5 Answers Found


Answer 1

Hello test_user_,

The key is in the proper mapping your data fields in the file to the table columns.

A not a bad walk-through is here: http://decipherinfosys.wordpress.com/2008/07/28/ssis-importing-data-from-a-text-file-using-a-package/

Besides, I assumed you load a tab delimited file to a SQL Server.

Not a bad idea to also get some tips on this technique, so here are some from BI Monkey: http://www.bimonkey.com/2009/06/flat-file-import-tips/

He also has a good set of other SSIS related articles (may be helpful).

Arthur My Blog
By: TwitterButtons.com

Answer 2

Hello, you can achieve this by two ways

1--Extract the data from flat file and then Pivot Transformation in ssis and finally load into your destination table. To configure Pivot Transformation is little confusing sometime when you are doing first time.

here is link , you can follow step by step to configure it.


2--Create your stating table 

CREATE TABLE dbo.MyStatingTable( Studentid int, StudentName varchar(30),Subject VARCHAR(30), Grades INT)

Load the data from flat file into this staging table, See the columns are exactly matching with flat file columns, just dumping data into staging table.

After dumping data , Write this TSQL Query and use this in Execute SQL task to load the destination table 

INSERTINTO dbo.MyDestinationTable
FROM  dbo.MyStatingTable PIVOT ( MIN(Grades) FOR Subject IN ([Science], [Biology], [Math]) ) AS PV 



Answer 3

Aamir is correct that in the example you gave us you want to use the Pivot transformation. However, are you sure you want to have 3 distinct columns for Science, Biology, and Math and not make that a 'Subject' or 'Course' column? Then you would have the final score in a 'Grade' column. Which brings me to point out that with the sample data provided you are not  placing the final column numbers anywhere...

Answer 4

I am trying to load data from a flat file. Here is some sample data:

100 John Science 75
100 John Biology 70
100 John Math     80

101 Dave Science 75
101 Dave Biology 70
101 Dave Math     80

I need to put this into a table called 'students' whose structure is: studentid, studentname, science, biology, math

is there anything in SSIS that I can leverage and get this done in an easier and efficient way - any input or help would be appreciated?


Two words. Pivot Transformation.



[Everyone else said the same but then said other stuff and I wanted to be succinct :) ]


Answer 5

Aamir - Thank you so much for your reply and the video. That solved my problem. Appreciate your help.





I am text mining e-mail messages.  I noticed that two sets each have rows containing "etc."  When the first set is processed "etc." is not found; however, "etc." is found in the second set.

Why is "etc." found in the second set, but not in the first?

The e-mail messages were loaded as one message per record into a field (ntext or DT_NTEXT).
I have read the MSDN article Term Extraction Transformation: http://msdn.microsoft.com/en-us/library/ms141809.aspx

The word and sentence boundary section did not clarify why different results are returned.  In each set "etc." appears without any boundary conditions other than a space, " ".  See Group 1: D and Group 2: B.  I would have expected that "etc." would have been found in group 1 with the selected criteria (see below) for Group 1.


Term Extraction Advanced Criteria:
Noun and noun phrase
Freq. threshold: 1
Max. length: 10
Case insensitive

Message Group 1
Records: 9
Total apearances: 7

Record: Frequency (unsorted, excludes records with out "etc.")
A: 2
B: 2
C: 2
D: 1

Record: Text string excerpt (unsorted, excludes records with out "etc.")

A: due to loss of steam from external supplier, etc.<crlf>
   reliable (such as new design or metallurgy for pumps, improved actuators on valves, etc.) you can exclude

B: due to loss of steam from external supplier, etc.<crlf>
   reliable (such as new design or metallurgy for pumps, improved actuators on valves, etc.) you can exclude

C: turnaround related costs (planning, execution, materials, etc.) occurring after the 1994
   these turnarounds (planning, execution, materials, etc.) is reported

D: shop or central shop overhead, buildings, supplies, vehicles, etc. can be reported

1: etc.
2: etc.<crlf>
4: etc.)

Message Group 2
Records: 35
Total apearances: 7

Record: Frequency (unsorted, excludes records with out "etc.")
A: 1
B: 1
C: 1
D: 1
E: 2
F: 1

Record: Text string excerpt (unsorted, excludes records with out "etc.")

A: additional data (#stages, total bhp, etc.)<crlf>

B: where pipeline pressure, flow, temperature, etc. are monitored

C: where pipeline pressure, flow, temperature, etc. are monitored

D: Control Facilities maintenance in Table 10-1, etc.  If you agree,

E: de-ethanizer tower, etc., should those
   table (i.e., line #33 for process driers, etc.)?  Please

F: has 1 set of dryers, reactors, etc.  Even though

4: etc.
1: etc.)<crlf>
2: etc.)

Thank you for your help!

Windows XP SP3
SQL Server 2008 Standard (10.0.2531)
Visual Studio Team System  2008 Developer Edition (9.0.30729.1 SP)
.NET 3.5 SP1


i am working on a data warehousing project for health care.i want to work on an architecture like this and need help...

1. source system is on another machine in sql server 2000.

2. data staging area database in sql server 2008 is used for temporary data stay on another machine.

3. target data warehouse in sql server 2008 is on third machine.

How to import data through ssis from a data table in sql server from machine and put it into sql server or any other RDBMS on another machine.


Hi everyone,

Ive been working with this package for a while... it was perfect until yesterday...I keep running my package and everything turns green... I have data on the source...but when I open the destination table is EMPTY !!! how come??   Does anyone knows what could be wrong?? thanks in advance...




Using SQL Server 2008 Express SP1 (sqlexpr.exe)

My command is sqlexpr.exe /X:C:\SQLInstall\

It works fine but shows the "Extraction Complete" message box at the end. If I change it to /Q /X:C:\SQLInstall\ then I do not see the progress bar or message box.

I wanted to set the parms such that I see the progress bar but do not see the Extraction Complete OK messagebox. How does one do that?

Have also tried /QS and /QN. They show same behavior (progress bar is seen, but so is the extraction OK message box at the end).


I am pretty much confused as to what to do; According to MS http://msdn.microsoft.com/en-us/library/ms345193.aspx, the disadvantages in making SSIS cluster aware seem to be greater than the advantages; all I want to do is to be able to run my DTS packages every time the cluster fails over to the inactive node; what is the best and safest approach?

This is my current environment:

We are using SQL Server 2005, 64-bit, on a Windows 2003 clustered server with Active/Passive 'A' and 'B' nodes. Databases are on a shared equallogic iscsi SAN.

Any help would be greatly appreciated




Is there any other simple way aside from creating an SQL Database and connect it visual studio to extract the test result? I only need the total time of every response in the link.


I created an ssis package to populate a SharePoint list (data destination) with data from a FoxPro database (data source).

I can run it in the development environment but each time the rows are appended to the list, making duplicates.

I want to run this automatically as a SQL Agent Job and the data must updated in the SharePoint list - NOT appended.

Can I get some feedback on where I need to focus my research on this?


Hi all,

I am working on Data migration using SSIS can anybody Please tell me how to migrate data from tally Data Base to Sql Server using SSIS 2008 and provide links that we migrate data from data sources to SQL server

please help me

Thanks in advance


Sunil yoganna


Hi all,

i working on Data Migration now i want migrate data from AS400 data base to Sql Server 2008

please help me and  if their is any sample package please provide me the link  and i want to migrate data from Cobal as well as MS access data base

thanks in Advance


Sunil Yoganna


Hi all,

i am working on data migration project now i want migrate data from SQL Server,FoxPro,Cobal& AS400.

any body please tell me  the tips tricks how to migrate  from this data base to SQL Server 2008 if their is any link regarding this pleas help me

out any Document with  examples for the above examples will be great help thanks in Advance.



Sunil Yoganna


Hello everyone,

I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step.  What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to call a proc that passes in a value of this data type.

ie:  CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS ....

In my SSIS package, I have the type defined as DT_STR with a length of 7. 

Now, when I run the package locally (via Visual Studio), the process runs with success.  However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with a "Invalid Parameter Number" error.

Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again.

Has anybody run into a similar issue or know what may be causing this issue?  I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's), however noticed that there isn't security tied to the types. 

Any other thoughts?  Please let me know if you need further explanation. 


Hi ,
Darren has already written an article on how to insert records into MS CRM using Webservice and SSIS , that clearly explains about the one time data load.

Is there a way to build on this and do a real-time data sync between MS CRM 4.0 and other data source(Text file ,any other RDBMS) .

If we can do this how can we kick start this package when a change occurs in MS CRM 4.0 , will that be through SQL Jobs or other means , will there be any issues.

After doing much research on this online , I found that their is very little written about this except Darren's article.

If anyone has any idea about this or  implemented this and was successfull or attempted it I found some issues ........Please share.

Thank You


how can i convert a record from a flat file to a different data type without using the data conversion task?

hi This my dynamci query baiscally what I am doing over here is I am taking query's where condition data from other server's table and i want to use in ssis


Here PROCESS is in server B

and dbo.P_COOPE  is in server A


So whatever opportunities i got from server B table PROCESS i want to use that records as a where condition in dbo.P_COOPE table  as where clause.

I have implemented this query in query analyzer Can you give me idea that how should i use it in ssis?

answer of query is

SELECT * FROM dbo.P_COOPE WHERE EXTERNAL_DEAL_ID IN ('0000169621','0000173296','0000185499','0000199013','0000207202','0000207546','0000207576','0000207673','0000216228','0000226684','0000230762','0000233095','0000253864')




SET @Opportunity_Id = ''

SELECT @Opportunity_Id =
                                    CASE @Opportunity_Id
                                          WHEN '' THEN ''''  + Opportunity_Id + ''''
                                          ELSE @Opportunity_Id + ','''  + Opportunity_Id + ''''
FROM Process




Reasonably new to SSIS and very new to the script component and VB Scripting, please be gentle.

I have the following style of Excel spreadsheet that I need to process

  <blank>   <blank>           12345        67890          (1 Column per Product for each sales date)

  <blank>   <blank>       20/06/2010   22/06/2010         

  StoreNo   StoreName     SalesQty     SalesQty

  100       City Central         20            5

  101       City East            15            6

  102       City North           10            7

I actually need the data at a CustomerNo/Product/SalesDate/Qty Level (to load into a DB Table) as such ...

100   City Central  12345  20/06/2010  20

101   City East     12345  20/06/2010  15

102   City North    12345  20/06/2010  10

100   City Central  67890  22/06/2010  5

101   City East     67890  22/06/2010  6

102   City Noth     67890  22/06/2010  7


The spreadsheet is dynamic in so far that it may have a variable number of columns or rows.  The other elements are consistent.

So I decided to use the Scripting component of SSIS to read the Excel Spreadsheet.

I have so far come up with the following code for the scripting component (with lots of help from Google)and it actually works (mostly) ...

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

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
  Inherits UserComponent

  Public Overrides Sub CreateNewOutputRows()
    Dim oExcel As Object = CreateObject("Excel.Application")
    Dim FileName As String

    FileName = "C:\TEST\SalesData_20100621.xls"
    Dim oBook As Object = oExcel.Workbooks.Open(FileName)
    Dim oSheet As Object = oBook.Worksheets(1)    ' The (1) Here denotes the FIRST WorkSheet

    Dim I As Integer
    Dim J As Integer

    Dim MaxRows As Integer
    Dim MaxCols As Integer

    For I = 7 To 1000             ' ROWS
      If oSheet.Cells(I, 1).Value = "" Then
        MaxRows = I - 1
        Exit For
      End If

    For J = 3 To 60               ' COLUMNS
      If oSheet.Cells(1, J).Value = 0 Then
        MaxCols = J - 1
        Exit For
      End If

    For J = 3 To MaxCols                ' COLUMNS
      For I = 7 To MaxRows              ' ROWS
        Output0Buffer.StoreNo = oSheet.Cells(I, 1).Value
        Output0Buffer.StoreName = oSheet.Cells(I, 2).Value
        Output0Buffer.ProductNo = oSheet.Cells(1, J).Value
        Output0Buffer.SalesDate = oSheet.Cells(4, J).Value
        Output0Buffer.SalesQty = oSheet.Cells(I, J).Value


  End Sub

End Class

Now for the questions ...

1.  I'm NOT using the Interop libraries, should I?  Any limitations with this style.  I only found out about these about 75% into the task.

2.  I'm working out the Column and Row Ranges myself.  Is this the right way?  I was planning on using the 'IsEmpty' method but could not get it to work(Script Editor does not recognise the command).  Also, in the calculation of the 'MaxCols' variable, it would NOT let me compare with an empty string, I had to use '0'.  Not sure why, but it gives me the required answer regardless.  Note that the data here is formatted as 'General' in the spreadsheet'

3.  If I run this 10 times, I end up with 10 instances of EXCEL running in Task Manager.  How can I get EXCEL to close down after executing the code.  The 2 lines of code at the end don't seem to do much.

4.  Any other suggestions?

Thanks in advance for any assistance.







I use execute SQL task to return a result and insert into a variable. The result will be in 2 rows. I set the variable to be a Object. The ResultSet in execute SQL task is Full Result set. However, I failed and the error message as below.


[Execute SQL Task] Error: The result binding name must be set to zero for full result set and XML results.

What is the problem. Please help



I have a sql query and I want to run the query against 10 different SQL 2000 servers.
The server names are present in a database table.
Basically the query should run for server1 and store the output in tableA, then the same query should run for the server2 and store the output in tableA, then the same query should
run for the server3 and store the output in tableA etc until we finish running for all the
10 SQL 2000 servers.

How can I do this using SSIS script task using VB script.



I have a maintenance plan created with management studio under maintennace plans, what it does under the hood is actually create a bunch of SQL agent jobs and also SSIS packages to run scripts etc.

The problem that I have now is that SSIS packages when created within SQL agent jobs have the server name  hardcoded into it. In my situation, i effectively clone the production environment for testing, and with the server name hard coded in the SSIS package, it means that when i clone the servers, i then have to manually change the server name to the name of the test server.

Is there a workaround to this issue ?



As I have mentioned in some of my other posts I am using SSIS 2005 to replace an existing MS Access 2003 / VBA based ETL engine which I developed some years back.   Part of my existing Access-based ETL performs a text search of the source records and I am now attempting to replicate that functionality in SSIS (replicate in terms of the end-result and not necessarily the methods used to get to that end result).  I have an idea of how I plan to go about this but since am relatively new to SSIS so would greatly appreciate the feedback of those more experienced...


In the source (Sybase ASE15) database, there is an "object" table (not the actual table name but for illustrative purposes it will suffice).  Within the object table there is a "description" column which is a char(60) datatype.  The description column simply represents a description of the object as defined by the source system end-user.

For my ETL solution I allow the ETL administrator to define one or many (1...n) key words or phrases which represent search criteria.  These search criteria are stored in a reference table in my target SQL Server 2005 database (the same database to which my ETL will transform results and store them).  My objective, is as follows:  For each of the 1...n search criteria defined, try and find that key word or phrase within the description column of the object table in the source database.  If a record is found that matches then tag that source record as a match (matches are used for later processing not important to the problem at hand).  This search routine must be performed for each of the 1...n keywords.  An example might be as follows:  The object table contains a record with the description: "This record is excluded from the contract".  One of the search criteria might be simply the word "excluded" or perhaps the phrase "record is excluded" either of which should result in a match in this case.


In Access 2003 I was able to accomplish this essentially by querying both my search_criteria table and the source database object table in parallel so as to create a result set of every matching criteira to every source object record simultaneously.  I guess it was a sort of cartesian product but it was actually reasonably fast.

In SSIS my initial idea is to use For-Each loops.  The first For-Each loop will use the search_criteria as the input and for each key word or phrase that is a search criterion, it will select from the object table description column of the source database to find a match.  I hope this select can be using a substring type function, trying to find the one string inside another.  Worst case I may need a second For-Each loop inside the first that uses each of the source object records as an input so I am doing a record by record check for each search criterion.  My concern with a double for-each loop is that it will be slow (I don't know this for sure I am just assuming it will be.


In searching for the string of text I also need to define whether a search keyword or phrase should be searched for case sensitive (or not) and whether or not the search keyword or phrase should be searched for as as an exact word or phrase.  For instance, if one of the search criterion was the keyword "Aug" (short for August) the ETL administrator might set the search on that key word to "exact match" so that the ETL search engine did not inadvertently pick up "Augment" as a match which contains the three letters "Aug" but means something different from "Aug" as in the month of "August".  In the Access world I handled case sensitivity through a switch in the VBA sub-string method I used and for exact match searching I added leading and trailing spaces.  There may be better ways of handling these things in SSIS but these were the best VBA / Access methods I could come up with.

While I am replacing an existing ETL engine, I do not want to design into my new SSIS package things that are essentially limitations of the previous technology used (Access 2003 / VBA in this case).  On the other hand Access had some surprisingly handy ways of handling some of these things and I tried to approach the problem in way that made good logical sense (perhaps beyond Access as a technology).


Any thoughts or feedback on this general problem - how to approach from a strategy stand point, methods or specific "SSIS tools / tasks" to consider using would be greatly appreciated.  After I hit "Submit" on this question I will start proceeding along the lines I discussed above but I will check back to see if there are suggestions on how I might better proceed.  Again, thanks in advance for any responses.




This is chandra,I am having one source table called EMP inthat i am having following records





1AAA100 -----






(Question 1)  


(Question 2)










Please give me the correct process to get result of the above questions do for me any body yar its urgent 



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure