2008 SSIS and NUMA Memory issues and BLOB Data Types

I have been reading the famous Integration Services: Performance Tuning Techniques document and I want to use the guidance in the Buffer Sizing section.

In order to optimize my settings for DefaultMaxBufferRows and DefaultMaxBufferSize, I need to calculate the Estimated Row Size for my Data Source.

However, when I look to the Integration Services Data Types document I find that several of the data types do not explicitly list their size in bytes.

Specifically:
DT_DATE
DT_DBDATE
DT_DBTIME
DT_DBTIME2
DT_DBTIMESTAMP
DT_DBTIMESTAMP2
DT_DBTIMESTAMPOFFSET
(DT_BOOL also isn't listed but the assumption must be it's one bit)

Does anyone know how big (in bytes) these data types are? The Estimated Row Size can't be found without them.

Thanks,

Peter Kral

How do I join a SSIS text field type cast from the Derived Column Transformation Editor with a varchar from an SQL data table in a lookup?

Also, is there any reference on which SSIS data flow datatypes can be JOINed to SQL Server datatypes?

Here's the problem:  I have records in a flat file.  The flat file name contains the data of the records.  When importing, I read the flat file name into a data field using SSIS.  Then, I use FINDSTRING to create a derived column that contains just the file date as YYYYMMDD.

There's a SQL Table that contains Currency Rates, indexed by currency code and date, in DD/MM/YYYY format, but it's stored as a VARCHAR

My problems:

A.) I tried to convert the string, '07302010' into the following formats, without success:

1. DT_DATE

2. DT_DBTIME

3. DT_FILETIME

4. DT_DBDATE

However, none of these can understand the string '20100730' as a date!

B.) Furthermore, if i convert the field into a DTW_STR, it still doesn't match the varchar(50) in my db.

So, basically, despite using all available typecast date formats, and several string formats as well, I can't find a way to

A.) Parse text out of a field

B.) Convert that text to either a DATE format or a TEXT format

C.) Use that resulting field to

Hello,

I am using SSIS 2008, I set a varaible @t1 to data type CHAR and set the value to Y next I used an Execute SQL Task with an ADO.NET connection to SQL 2008 R2 database to insert this the value of @t1 into a test table with only 1 column of data type NCHAR(1):

insert into test1
values(@t1)

On the Parameter Mapping section of this task I put the Data Type as String and initially set the Parameter size to -1  

The only problem is it tries to insert a 2 character numeric value into the SQL table. If I set the value of @t1 to N the parameter mapping seems to convert this to 78, when I set the value of @t1 to Y the parameter mapping converts this to 89 it looks like it converts the character to an ASCII code.

Is this meant to be the intended behaviour, as all I wanted to do was insert the actual character into the destination column and not the ASCII code?

 dtl1

I am looking for best solution to perform incremental data load from MySQL database to SQL Server 2008.  I tried to follow instructions from articles on incremental data load in SSIS  using MD5 hashes but I failed to make connection in Lookup transformation editor between MySQL Id (4 byte unsigned integer) and Id in SQl Server 2008 table which is  bigint. i tried to add  data conversion and convert MySQl Id to string. It works fine when you map this field directly to OLEDB destination but in Lookup component it failed. 

Any input greatly appriciated.

I have a simple solution with (right now) 4 packages, 3 configuration files and one "global" datasource.  Here is the View Code on the data source:

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" xsi:type="RelationalDataSource" dwd:design-time-name="02d8345e-665e-4bd8-9323-1e846af220e5" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ID>PPDM37 Staging</ID>
  <Name>PPDM37 Staging</Name>
  <CreatedTimestamp>0001-01-01T07:00:00Z</CreatedTimestamp>
  <LastSchemaUpdate>0001-01-01T07:00:00Z</LastSchemaUpdate>
  <ConnectionString>Provider=SQLNCLI.1;Data Source=engsql03.dev.ihs.com;Persist Security Info=True;Password=;User ID=;Initial Catalog=PPDM37_Staging</ConnectionString>
  <ConnectionStringSecurity>PasswordRemoved</ConnectionStringSecurity>
  <Timeout>PT0S</Timeout>
</DataSource>

As you can see, the <ConnectionStringSecurity> node is set with PasswordRemoved.  When editing the data source and checking the Keep Password option on it still saves wit

Okay so i have a physical server i am trying to replace with a virtual server. Its doing a large load process every night, about 190GB on one disk.

The VM is:

On vSphere 4.1 ESXi

O/S is Windows Server 2008 R2 Enterprise

SQL version is SQL Server 2008 R2 Enterprise.

4 vCpus

Now 20GB of memory

Disks assigned to this virtual are all seperate LUNs except for the O/S which is on a shared LUN which is not busy. So 1 LUN/Datastore per 'disk drive on this SQL virtual. Data/Log/TempDB/Backup are on their own LUNs.

All the recommended exclusions are in place for McAfee on a SQL Server.

ESXi 4.1 Enterprise host is not overcommitted. Has 4 x 6core 2.6Ghz AMD processors. BL685c G6 and 128GB of memory. Almost nothing else is running at the time i am testing this load and host is not overcommited or stressed or anywhere near it.

SQLServer process shows 127,236K Working Set and 130,040K Peak working set and memory Private working set 88,828k and Commit Size of 302,792k.

It seems when i check the memory usage in task manager it doesn't show much. In Windows 2003 X64 in would show the GB being used, like 8GB right in task manager. But i don't see that in Windows 2008 R2 task manager. I downloaded SysInternals and it shows the memory on the Windows 2008 r2 box as 15Gb of AWE memory. How can this be? The case is th

Hi , I declared a user variable MINIODate  as string in package level and assigned this variable in an execute sql task 2008 , where my source is ado.net and sql command is Select CSTR(MIN(IODate) ) as MINIODate  from IOData ( This is source table available in MSAccess DB) , The above senario working fine in 2005. During the run time in 2008  i am getting the following error

[Execute SQL Task] Error: An error occurred while assigning a value to variable "MINIODate": "The type of the value being assigned to variable "User::MINIODate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Hi

We are having 20 dimension tables and each table will be having around 20 million records.

These tables would be loaded on a daily frequency with 5 files, each of 3 million records.

We are currently using SCD transformation for TYPE2 load of data.( to maintain history in the dimension table.)

But SCD is taking a long time to insert the data and below are the statistics that I recorded when I executed the package with sample files:

Run1: File1(0.5 million records)  -2 minutes  (Dimension Table is empty)
Run2: File2(0.5 million records)  -13 minutes (Table has  589,000 records)
Run3: File3(0.5 million records)  -26 minutes (Table has 1,140,000 records)
Run4: File4(0.5 million records)  -37 minutes (Table has 1,680,000 records)
Run5: File5(1 million records)   -51 minutes (Table has 2,780,000 records)

Package elapsed time : 2 hr 9 min

1. How do i improve the performance of the SCD? If not, is there any way of loading a table parallely from file so that i can achive performance?

2. In informatica, we have a partitioning feature to load the data parallely which greatly improves performance. Is there any equivalent feature or workaround in SSIS?

Any help would be greatly appreciated.

Thanks,
Bhaskar