Tag Archive : SSIS

SSIS import mixed datatypes from Excel to SQLServer

Posted on October 7, 2010 in: SQLServer 2005, SSIS|Post Comment

1
Digg me

While importing data from Excel to SQLServer, if a Excel column in has both Text and Numeric values then either Text or Numeric values will be NULL in SQLServer.

See example..

If first data cell (in Excel) in that particular column is of type String/Text then all Numeric values in that column will be NULL in SQL table or viceversa.

This NULL can be avoided in 3 different ways

1. Manual Change

Manually add single quotes (') before each Numeric cell and convert it to String / Text type. This way all the values will be exported to SQLServer without NULLS.

2. Excel to Access to SQLServer

If number of rows to modify is more then …

First export the excel data to MS-Access database and then export Access DB to SQL Server. This way all the values will be exporeted to SQLServer without NULLS.

3. Change EXCEL Connection String

This is simple method but it has its own disadvantage.

Append IMEX=1 to ExcelFileConnectionString. Adding IMEX=1 changes ISAM driver to Import Mode.
(click on the image for full view)

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.

The possible settings of IMEX are:

0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)

More Info :

http://support.microsoft.com/kb/194124/en-us

http://www.jimmcleod.net/blog/index.php/2008/06/25/importing-excel-spreadsheets-into-sql-server-2005-ssis/

Rearrange SSIS Packages in SSIS Project.

Posted on May 6, 2010 in: SSIS|Post Comment

1
Digg me

If you create multiple packages in a Project, SQLServer Business Intelligence will not sort them based on file name. 
There is an alternate way to sort the items in the way you need.
Open .dtproj file in Notepad / Notepad ++ 
Scroll down to <DTSPackages>
Example :
 <DTSPackages>
    <DtsPackage>
          <Name>20-Export-To-Production.dtsx</Name>
          <FullPath>20-Export-To-Production.dtsx</FullPath>
          <References />
    </DtsPackage>
    <DtsPackage>
      <Name>10-Import.dtsx</Name>
      <FullPath>10-Import.dtsx</FullPath>
      <References />
    </DtsPackage>
  </DTSPackages>
You will see XML elements with SSIS package names. Cut and paste them in the order you want and save the .dtproj file.
   <DTSPackages>
    <DtsPackage>
      <Name>10-Import.dtsx</Name>
      <FullPath>10-Import.dtsx</FullPath>
      <References />
    </DtsPackage>
    <DtsPackage>
          <Name>20-Export-To-Production.dtsx</Name>
          <FullPath>20-Export-To-Production.dtsx</FullPath>
          <References />
    </DtsPackage>
  </DTSPackages>
Make sure you are cutting and pasting them with opening & closing tags.
Close Notepad.
Open your Project using Sqlserver Business Intelligence Studio
You will see the packages rearranged in desired sequence.

Where is the Connection String ?

Posted on May 27, 2009 in: SQLServer 2005|Post Comment

3
Digg me

Is this a bug in SQL Server 2005 Sp3 or a feature ?

SQL Server 2005 Management Studio Sp2

SQL Server 2005 Management Studio Sp3

Where is the Connection String ? How are we supposed to import data from ODBC data sources ?

Unable to instantiate XML DOM document : SSIS 2005

Posted on April 27, 2009 in: Error Messages, SQLServer 2005, Tips and Tricks|2 Comments

0
Digg me

SSIS 2005 Business Intelligence
Unable to instantiate XML DOM document, please verify that MSXML binaries are properly installed and registered.
 Other variations are  Failed to save package file “.dtsx” with error 0x8002801D “Library not registered.”

The reason you see this error is because MSXML got corrupted.

The fix is

1. Close Studio / Business Intelligence

2. Download Latest MSXML 4.0 (sp3)  as of today.

3. Install it. Done

2a. Some cases you may need to install MSXML 6.0.