ssis

how to skip from a row in script component

How to skip some records in script component without using conditional split component? ...

Grouping in SSIS

So, I have a bunch of data that I'm trying to import using SSIS. The problem I'm having is that some of the data is outdated. So I want to only import the most recent data. I have a key that indicates which set of data each row belongs and I only want to import the most row per key. What is the best way to do this in SSIS? My only...

Analysis Service Processing Task Issue

I have used Analysis Service Processing task. Sometimes I get process failed when executing this task. But when I manually execute the OLAP DB, through Analysis Services, process runs successfully. Then the above said Task executes successfully. So what is the correct settings to be given in this task, to work just like manually executin...

Cannot read Excel files in SSIS 2008

Hi all, I'm stuck trying to get SSIS 2008 to read an Excel file. Yesterday, I migrated our SSIS project from 2005 to 2008, and though the upgrade said it worked, I cannot get it to read an Excel file. I read online that I needed to upgrade the JET drivers to Office 2007 (AccessDatabaseEngine.exe), which I did. And voila! Everything s...

SSIS flat file export timestamps have excess precision?

SQL 2008 R2, have a simple export to a Flat File Destination. Timestamp columns are set to DT_DBTIMESTAMP in the destination, which according to the docs "The fractional seconds have a maximum scale of 3 digits." However, my exported files have 7 digits of fractional second precision, as defined by DT_DBTIMESTAMP2 in the docs. The column...

Error handling in DTS Package execution using .NET

Hi, I am executing a SQL Server 2000 DTS package using C# by following the code from this article http://support.microsoft.com/kb/319985. once the package is executed I am looping through each step to find out if any step has failed and get info about error if it has failed. I also use this information to find out if the package has succ...

Converting data types in SSIS

I get an input CSV file that I have to upload to my oracle database. Here is some sample data ContractId, Date, HourEnding, ReconciledAmount 13860,"01-mar-2010",1,-.003 13860,"01-mar-2010",2,.923 13860,"01-mar-2010",3,2.542 I have to convert the incoming column to DB_TIMESTAMP (to match the structure in the destination table). But w...

How to create an SSIS custom task that references another library

How can one successfully reference a library from an SSIS Task library? EX: MyCompany.SsisTasks.AwesomeTask have a reference to MyCompany.SsisTasks.Common? When I try to do this, this is what pops up: Could not load file or assembly 'AwesomeTask, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. The system cannot...

SSIS FileNameColumnName output value incorrect

Hi, In a Flat File Source in SSIS I am trying to use the FileNameColumnName property (from Advanced Properties) to populate a field in a database. It seems quite straightforward - I simply gave it a name and mapped it to a field in my OLE DB. The problem is, however, that it DB field is populated with the letter 'C', and nothing more....

ETL interview questions?

In 5 days I'm going to ETL interview. It's my first interview on this subject. What question would I be asked? Most likely they will be about MS SQL Server Integration Service. If possible, provide the answers. =) ...

What variable type use to represent a Money in SSIS

I need to load a Money value in a variable in SSIS using a "Execute SQL Task" component. I map the return column of a SELECT to a variable. I've declared the variable "UnknownMoney" as a Double or a Single, but i always receive the error: Error: 0xC002F309 at Load Dummy vars, Execute SQL Task: An error occurred while assigning a value t...

Where is SSIS in Visual Studio 2010?

Where do I find the SSIS tools in Visual Studio 2010 Ultimate? I'm at a new job so new computer / new setup / new everything. I can't find them anywhere! Is there a specific edition of SQL Server that includes these tools? The DB is SQL Server 2005, SSMS is 2008, VS is 2010. ...

Parse column value based on delimeters

Here is a sample of my data: ABC*12345ABC BCD*234() CDE*3456789(&(& DEF*4567A*B*C Using SQL Server 2008 or SSIS, I need to parse this data and return the following result: 12345 234 3456789 4567 As you can see, the asterisk (*) is my first delimiter. The second "delimiter" (I use this term loosely) is when the sequence of numbers ST...

SSIS dynamic excel file load

Hi, I have a range of excel files that I need to load into the database. Each file can have a varying number of columns and varying column names. I hold the mapping between the file column names and db column names in a sql table. I want to extract specific columns in each file and then save them to a common sql table. SSIS lets you ext...

DTExec will not run without a window

I am running SSIS from a C# application. I would like to run the SSIS process using the dtexec utility but without showing the dtexec window when running. I have set the ProcessStartInfo.CreateNoWindow to true yet dtexec still displays the window. I assume this is due to dtexec and not C#, but I cannot find a argument for dtexec that ...

Export Import error 'SSIS Data Flow Task could not be created' … registering DTSPipeline.dll, cannot create task “STOCK:PipelineTask”

I'm about to throw in the towel on this one. Running SQL Server 2008 enterprise on Windows 7 x64. Can't get past this issue. When I try to Import / Export Data from databases through SQL Server Management Studio I get the following Error. Error: TITLE: SQL Server Import and Export Wizard ------------------------------ The SSIS Data ...

SSIS 2008 and Undouble

I am using SSIS with SQL Server 2008 R2. I am importing large-ish text files (approx 0.5 Gb), and these files use double quotes as a text qualifier, and the comma as a field delimiter. Many of the files have text qualified fields and include a comma between the terminating double quotes. SSIS should interpret this is a single field, (...

When I add or derive columns, subsequent data flow transformations don't see the new columns

Is there a way to "cascade" new column information down through the subsequent data flow transformations when I add a new column? My current practice is to open each tranformation in sequence and verify that the new column is available. If it isn't, I delete and recreate the transformation. I can't believe there isn't a better way to ...

SSIS, Dealing with rows which depend on other rows.

This is quite a strange problem, wasn't quite sure how to title it. The issue I have is some data rows in an SSIS task which need to be modified depending on other rows. Name Location IsMultiple Bob England Jim Wales John Scotland Jane England A simplifed dataset, with some names, their locations, and a column 'IsMultiple' whi...

SSIS XMLSource only seeing null values in XML variable

I have a Data Flow task with an XMLSource that references an XML Variable. The DataFlow task does recognize that there are x number of rows in the variable, but it only sees null values in every row: The xml variable value: <?xml version="1.0" encoding="utf-8"?> <words> <word>butter</word> <word>crispy</word> </words> I used th...