Tag: SSIS

Update / Insert ( UPSERT ) MySQL Table From SQL Server Table – SSIS

Posted on by Reza Rad

There are lots of links which described how to UPSERT( Update / Insert ) between two SQL Server tables. which mostly used Lookup Transform with OLEDB Command, like this:http://www.insideit.fr/post/2010/06/17/Lookup-simple-sur-SSIS But when you work with MySQL there is a limitation, you can not use OLEDB Connections, you can use ODBC or ADO.NET Connectors from .NET to Read more about Update / Insert ( UPSERT ) MySQL Table From SQL Server Table – SSIS[…]

Find Last Created File in Special Directory – SSIS

Posted on by Reza Rad

There are lots of time which you want to get latest created file in a directory and do something with it, for example do a data transfer or do a file system task like delete or move. As you may know the Foreach Loop Container in SSIS can loops through files of a directory, But Read more about Find Last Created File in Special Directory – SSIS[…]

Loop through FTP Files On Remote Folder and Delete OLD Files – SSIS – .NET Code

Posted on by Reza Rad

Few days ago, I encounter a request which asks for delete files on remote ftp folder which are older than 10 days with SSIS tasks. Suppose date of each file is embeded in the file name like this: zzz_2010-10-25 03-00-22.txt As you may know , using Foreach loop with configuring enumerator as file enumerator will Read more about Loop through FTP Files On Remote Folder and Delete OLD Files – SSIS – .NET Code[…]

Transfer Multiple Files from or to FTP remote path to local path – SSIS

Posted on by Reza Rad

This is a common mistake which SSIS developer’s thinks that they need Foreach Loop for transfer multiple files from ftp to local or from local to ftp.I had a post about using masks in FTP task for transfer multiple files, but it seems that I should clarify this more than before. Note that you don’t Read more about Transfer Multiple Files from or to FTP remote path to local path – SSIS[…]

Export Data Starting From Special Cell in Excell File – SSIS

Posted on by Reza Rad

Sometimes you have a formated Excel file, which you want to use this file as Destination, so you may need to import data start from special Cell inside the excel file.By default SSIS import data from Column1 and Row1 in excel file. But you can change it. Solution: Suppose this excel format: in this sample Read more about Export Data Starting From Special Cell in Excell File – SSIS[…]

SSIS – Sql Server to XML – Save to file

Posted on by Reza Rad

As you know there is no XML destination in SSIS. so how you can save result of a query from sql server to XML file? Solution is: First Of all you can use FOR XML to get result of query in XML , look at our sample query : SELECT EmployeeID,NationalIDNumber,ContactIDFROM HumanResources.Employee FOR XML RAW(‘Facility’),Root(‘Extract’),Elements  Read more about SSIS – Sql Server to XML – Save to file[…]

Output Parameter of Stored Procedure In OLE DB Command – SSIS

Posted on by Reza Rad

I encounter a question today which was about using a Stored procedure inside a Data flow task, and use result of stored procedure which returned by output parameter beside other columns in Data Stream. Solution with sample: Suppose your stored procedure has an input parameter and output parameter. I show you a simple stored procedure Read more about Output Parameter of Stored Procedure In OLE DB Command – SSIS[…]

How to handle Failed Rows in a Data Flow

Posted on by Reza Rad

suppose this scenario: you have a source table and a destination table, you want to transfer rows from source table to destination table, so you can use simple data flow with OLE DB source and OLE DB Destination. but problem hits when destination table has few rows inside, and there is Unique Constraint in ID Read more about How to handle Failed Rows in a Data Flow[…]

Looping through specific files – SSIS

Posted on by Reza Rad

As you may know, there is an enuerator in Foreach Loop Container which help you to loop through files, named File Foreach Enumerator. you can specify a location for searching through files and get files as enuerator in loop.But, there are some times which you need to loop through specific files not all of files Read more about Looping through specific files – SSIS[…]