Being a dumb ‘merican, I think the lower case thorn character is 0xFE while upper case is 0xDE. This will become important soon. I created an SSIS package with a Flat File Connection Manager. I pointed it at a comma delimited file that looked like col 1,col 2,col 3 This allowed me to get the … Read more
Here is one possible way of loading this file into SQL Server. Below shown example reads the contents of EDI 823 Lockbox file and loads into multiple tables along with the relationship. I am sure that there are other better ways of doing this. This is just one example of loading an EDI file into … Read more
If you create a similar table, you can use it in 2 approaches to map columns dynamically inside SSIS package, or you must build the whole package programmatically. In this answer i will try to give you some insights on how to do that. (1) Building Source SQL command with aliases Note: This approach will … Read more
Summarized: INSTALL 32 bit version of Microsoft Access Database Engine 2010 Redistributable. Uninstall 64 bit version if previously installed. http://www.microsoft.com/en-us/download/details.aspx?id=13255 The Excel connection manager is trying to use the ACE OLE DB provider in order to access the Excel file when the version is above 2007 (xlsx). Although your box is 64-bit, you’re using SQL … Read more
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on … Read more
The first error I would address is “The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.” The out of the box Excel drivers only exist in the 32 bit address space. BIDS/SSDT is a 32 bit application so Excel source and destinations work just … Read more
I have no SSIS experience but as an ETL developer I have faced this many times. So my suggestions might not help you solve the problem but hopefully point you in the right direction If the problem field has text qualifier (single or double quote usually) and SSIS supports use it Also if there is … Read more
SQL server provide assemblies to manipulate packages programmatically. To do a reverse engineering (deserialize a dtsx package), You have to do this by looping over packages and read them programmatically, just follow this detailed link Reading DTS and SSIS packages programmatically There is another way (harder way and not recommended) to achieve this , by … Read more
There is 3 Methods to achieve this: 1st method – Using Lookup Transformation First you have to add a Lookup Transformation like @TheEsisia answered but there are more requirements: In the Lookup you Have to write the query that contains the ID list (ex: SELECT ID From MyFirstDB WHERE …) At least you have to … Read more
Here is a possible option which demonstrates how to load multiple XML files having same definition into an SQL Server table. The example uses SQL Server 2008 R2 and SSIS 2008 R2. The example shown here loads three XML files into an SQL table using SSIS Data Flow Task with the help of XML Source … Read more