How to read mixed data type from excel file in SSIS

To fix the issue where SSIS was reading mixed data  type wrong from an EXCEL file I had to change the registry information
I had a file where the source file had account numbers where some rows had value as integer and some had alpha numeric character. When the account number had first few rows as integer the remaining rows with the alphanumeric account number value was not getting read and the value was getting imported as NULL. I followed the following steps to fix this issue in EXCEL 2012 .

1. In Windows environments, select Start ► Run and type REGEDIT to display the Registry Editor.
2. In the registry tree, select HKEY_LOCAL_MACHINE ► Software ► Wow6432Node ► Microsoft ► Office ► 14.0 ► Access Connectivity Engine ► Engines.
3. Double-click the Excel node.
4. In the right panel, double-click the TypeGuessRows entry.
5. Change the value data from 8 to 0.
6. Click OK.
7. Select File ► Exit to exit the Registry Editor window.

This modification instructs Excel to scan all of the rows in the table for their data types. This action might decrease performance on very large tables, but the importation of data will be more accurate. You can also set TypeGuessRows from 0 to 16, inclusive, where 0 scans all of the rows and the other numbers set the number of rows to scan first.

How to add the source file row number to the destination table.

https://www.hansmichiels.com/2015/11/13/how-to-add-a-rownumber-to-a-data-flow-task-in-ssis-2012/#comment-205

The above link has all the explanation. It was very helpful.

Generate XML data

--XML data sample to be genereated

<?xml version="1.0"?>
-<serialization SerializationRoot="SBOInboundBatchCO">

-<SBOInboundBatchCO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO">

-<sboInboundBatchIO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO">
<creationDate Type="DATE">2017/05/02</creationDate>
<externalReferenceDate Type="DATE">2017/05/02</externalReferenceDate>

-<sboInboundMessageIOs ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO" IsIndexed="1">

-<element ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO">
<billingPeriodsequenceNumber>2</billingPeriodsequenceNumber>
<messageCodeBehaviourCode Type="STRING">CACISBOACKGMENT</messageCodeBehaviourCode>
<messageText Type="STRING"/>
<receivableGrpId Type="STRING">615466</receivableGrpId>
<responsiblePartyIdentifier Type="STRING">Y</responsiblePartyIdentifier>
</element>

</sboInboundMessageIOs>
<sboName Type="STRING">CACi BD Collections</sboName>
<totalMessageCount Type="NUMERIC">284</totalMessageCount>
</sboInboundBatchIO>
</SBOInboundBatchCO>
</serialization>

Used the following SQL script to get this output

declare @intTotMsgCount int , @xmlstr xml



　
 set @inttotMsgCount = (Select count(Account_number) from Imp_staging_referrals_claims_FollowUp where imp_fileId = @intfileId

and uploaded = 1 and deleted = 0)



　
 set @xmlstr=



　
 (select 'SBOInboundBatchCO' as '@SerializationROOT',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO' as '@ClassName',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO' as '@ClassName',

'DATE' as 'creationDate/@Type',

convert(varchar(10),cast(getdate() as date) ,111) as 'creationDate',

'DATE' as 'externalReferenceDate/@Type',

convert(varchar(10),cast(getdate() as date) ,111) as 'externalReferenceDate',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName', '1' as '@IsIndexed',




(
  select ' com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName',

'1' as 'billingPeriodsequenceNumber',

'STRING' as 'messageCodeBehaviourCode/@Type',

'CACISBOACKGMENT' as 'messageCodeBehaviourCode',

'STRING' as 'messageText/@Type',

'' as 'messageText',

'STRING' as 'receivableGrpId/@Type',

substring(Account_Number,2,len(Account_Number)-4) as 'receivableGrpId',

'STRING' as 'responsiblePartyIdentifier/@Type',

'Y' as 'responsiblePartyIdentifier'

from Imp_staging_referrals_claims_FollowUp

where imp_fileId = @intFileId

and Facility_Code = @intClientCode



　
 for xml path ('element') , elements,type




)
  for xml path ('sboInboundMessageIOs') , type

)

,'STRING' as 'sboName/@Type',

'CACi BD Collections' as 'sboName',

'NUMERIC' AS 'totalMessageCount/@type',

@intTotMsgCount as 'totalMessageCount'

for xml path('sboInboundBatchIO'), elements,type




)
  
for xml path('SBOInboundBatchCO'), type




)
  for xml path ('serialization') , type




)
  --AS COL_XML
  
select '<?xml version="1.0"?>' + cast(@xmlstr as varchar(max)) as COL_XML

How to export data from database tables to an XML file using SSIS

I had a project where I had to generate a xml file with data from database tables using SSIS.

The components involved were

Data Flow Task
OLE DB Source - which had the SQL command at the stored proc that called the XML data
Flat File Destination  that used a Flat File Connection Manager.

The important thing to remember here was the  FFCM set up .
Here are the screen shots to show I set it up .

The DFT consisted of OLEDB Sour Editor calling a procedure that generated XML data.

Below is the Flat File Connection Manager used. The format was DELIMITED.

 

 

In the Columns section a new column COL_XML is added which is the ONLY column returned by the stored procedure that has the XML data.

 

 

 

 



The final important part was the below where we set up the data type which had to be Unicode text stream

Error: Tranaction log full due to replication

I had this error in one of the databases and I was trying different possible steps to fix it.

I tried to take a transactional log back up - no luck
I tried to change the recovery model to simple - no luck
The database does not have replication but it does have CDC enabled. So I tried to disable the CDC I got another error related to metadata
I tried to change the db owner to sa - no luck still error
Then the following helped me to get rid of the error that was saying replication

EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1;

I tried to change the db owner to sa after running the above - success
I tried to disable the CDC - success
I expanded the log file - success

This is how I was able to resolve the issue.

MSDB too large

C drive was growing very fast and then saw that the msdb  data file and log file was huge.

I shrank the msdb log file first.
I ran the following query to see the top higly populated table

SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO

Once running the query on MSDB , I found the follwing large tables

I ran the follwing to clear the history

use msdb
GO
exec sp_maintplan_delete_log @oldest_time = '05/12/2017'


This expands the log file so make sure you shrink file


DBCC SHRINKFILE(MSDBLog, 50) --50MB

GO


Then the following maintenance plan was scheduled to run every week.