Loading Data File In SSIS With Multiple Record Layouts
Apr 4, 2007
I am trying to load a file using SSIS that contains records with two different layouts in one data file but in the flat file connection I can only specify one layout and this is causing the records with the second layout to be loaded incorrectly.
The different record layouts can be identified by the first character of the record. Example: If Field begins with "A" then assign one layout; "B" assign second layout.
Has anybody come accross this issue, if so some guidence would be appreciated.
We have a flat file format generated from a vendor. It contains a "mainframe" view of the data with a header record, batch header record, detailed records, batch trailer record and trailer record. It arrives as a .dat file. What is the best approach to extract the necessary columns out of this file to populate the corresponding SQL server tables and rows?
I have multiple xml data file in a directory say C:XMLData abc1.xml, abc2.xml, abc3.xml etc.
Need to loop through each file in ssis with Foreach loop container, and get the file name say abc1, and load the data of abc1.xml to abc1 table in sql server DB.
Next iteration will pick up the abc2.xml and find the abc2 table in sql server DB then insert the data in abc2 table.
While each iteration, xml source should also point each xsd file correspondingly.
 Tables are already created in DB
I solved my problem up to getting the file name from ech iteration and assigned file name to variable, in oledb destination data access mode I select Table or view name variable, then corresponding table will get selected for data insertation.
Just wanted to know how can I read each xsd file for each xml data files while iteration.Â
Col 9 is char (2) and Col10 is char (34). It is this column that needs to be broken up into several columns depending on the value in Col 9.
Col1 to Col3 is the key to the record.
So say if record 1 has Col 9 value 'AA' then Col10 ( 34 bytes) is to be spilt into 10+10+10+4 (four columns).The value 'AA' can repeat for several records and the value in Col 10 can change for the same value 'AA'.
Now say record 27 has Col 9 value 'BB' then Col 10 is to be split as 5+25+4 (3 columns).
There are 15 such unique values of Col 9. I have the file layouts for Col 10 for each distinct value of Col 9. So using the file layouts and Table A which exists in my database how do I proceed.
Need I make 15 tables ( one each for the 15 unique Col 9 values). These structure Col1..Col2..Col3...Col9 (the key fields and Col 9) will be common to every table. Plus the file layouts will serve as additional columns specific to each of these tables.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Hi All, i have mutiple text file. let us say,a1.txtb1.txtc1.txt i have to port this text file data into the table (SqlServer Database) which have the same file structure.(i.e)x1 (SqlServer table)y2 (SqlServer table)z3 (SqlServer table) now i have to transfer a1.txt file data ----to--- x1b1.txt file data ----to--- y2c1.txt file data ----to--- z3 using SSIS. like that, i have to transfer more than 250 files at a time.manually binding 250 files into the package is very cumbersome and time consuming process. so, can any one give ur valuable sugession to solve this issue.
We did the development of SSIS packges on 32 bit machine. We have few excel files which is loaded using SSIS. Now the same was deployed to anothe rmachine(64 bit). This 64-bit machine does not have Microsoft office installed. And all packages(which loads excel files) failed. Hence can someone answer my following questions:-
1) In order to load excel files using SSIS, is it necessary that Microsoft excel software should be installed on that machine?
2) If answer to above is yes, Can Microsoft excel viewer be used instead of Microsoft office(excel)?
I have just loaded SQL Server 2005 SP1 and it is playing havoc with any SSIS packages that use the File System Task.
I am using the FST to copy a file to a directory after it has been loaded. This worked fine prior to SQ1 but now I am getting the following error if there are one or more files already in the target directory:
[File System Task] Error: An error occurred with the following error message: "The directory is not empty. ".
If I remove all files from the directory it works fine.
Has anyone come across this problem and got a workaround for this? Will it involve me writing a FSO script task???
I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes. The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length.Â
Can SSIS be used to created these two tables? (one without  description fields, the other with those field but arranged vertically in the table rows).
The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.
I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:Â
I am trying load data from multiple Foxpro tables which are under a folder. I can have multiple folders with 17 foxpro tables. I was able to do it in DTS using ActiveX script. Here is the ACtiveX script.
'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Option Explicit Dim conObj,DSNGosfbill,comObj,objRs,HostServer Dim sFolder,sFileFolder, Details,subFolderoccur,sFileFolderDBF,sFileFolderFPT,CheckFile,dFiles,Fil Dim fso, folderObj,subFolderList,dFolderObj Dim objPackage,oStep,objPackage_1,oStep_1,ConnObj_001,ConnObj_004,ConnObj_031,ConnObj_032,ConnObj_033 Dim ConnObj_Hclaimb, ConnObj_HProv, ConnObj_Hids, ConnObj_HCodes, ConnObj_HSpan, ConnObj_002, ConnObj_HCHGB Set conObj = CreateObject("ADODB.Connection") HostServer =DTSGlobalVariables("gvServer").Value
set comObj=CreateObject ("adodb.command") set comObj.ActiveConnection =conObj Function Main() Dim Dir_Name,DirFlag Dir_Name = "" DirFlag = "N" Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(checkFile) Then Else Details = "***** Success.Lst file is missing in Batch folder. BATCH job may not be successfull or there are no folders in UNZIP directory to process. Check the batch run.*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Set folderObj = fso.GetFolder(sFolder) Set subFolderList = folderObj.SubFolders For Each subFolderOccur in subFolderList DirFlag = "Y" Dir_Name = subFolderOccur.Name Call Process_Dir(1,subFolderOccur.Name) Next If DirFlag = "N" Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If If DirFlag = "Y" Then Call Process_Dir(2,Dir_Name) If objRs.Eof Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If While not objRs.EOF set sFileFolder = fso.GetFolder(sFolder & objRs("zip_file_name")) Details = "***** Start-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log Call Update_Process_Flag("L",objRs("zip_file_name")) '*******Execute the package for each directory****************' '********* Call the Package**************' Set objPackage = CreateObject("DTS.Package") Set objPackage_1 = CreateObject("DTS.Package")
Set ConnObj_001 = objPackage.Connections("SSIPATH001") ConnObj_001.DataSource = sFileFolder
Set ConnObj_002 = objPackage.Connections("SSIPATH002") ConnObj_002.DataSource = sFileFolder
Set ConnObj_004 = objPackage.Connections("SSIPATH004") ConnObj_004.DataSource = sFileFolder Set ConnObj_031 = objPackage.Connections("SSIPATH031") ConnObj_031.DataSource = sFileFolder Set ConnObj_032 = objPackage.Connections("SSIPATH032") ConnObj_032.DataSource = sFileFolder Set ConnObj_033 = objPackage.Connections("SSIPATH033") ConnObj_033.DataSource = sFileFolder
Set ConnObj_Hclaimb = objPackage.Connections("SSIPATHCLAIMB") ConnObj_Hclaimb.DataSource = sFileFolder
Set ConnObj_HProv = objPackage.Connections("SSIPATHPROV") ConnObj_HProv.DataSource = sFileFolder Set ConnObj_Hids = objPackage.Connections("SSIPATHHIDS") ConnObj_Hids.DataSource = sFileFolder Set ConnObj_HCodes = objPackage.Connections("SSIPATHCODES") ConnObj_HCodes.DataSource = sFileFolder Set ConnObj_HSpan = objPackage.Connections("SSIPATHSPAN") ConnObj_HSpan.DataSource = sFileFolder
Set ConnObj_HCHGB = objPackage.Connections("SSIPATHCHGB") ConnObj_HCHGB.DataSource = sFileFolder
objPackage.Execute For Each oStep In objPackage.Steps If oStep.ExecutionResult = DTSStepExecResult_Failure Then Details = "***** GOSFBILL_SSI_Staging_Load failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
For Each oStep_1 In objPackage_1.Steps If oStep_1.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Update_FileSource failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
'********************************************' Details = "***** End-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log objPackage.Uninitialize objPackage_1.Uninitialize Set objPackage = Nothing Set objPackage_1 = Nothing sFileFolder = "" sFileFolderDBF = "" sFileFolderFPT = "" objRs.MoveNext Wend objRs.Close End If Call Close_Conn Main = DTSTaskExecResult_Success End Function Sub Process_Dir (Para_cntl,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Dir" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Para_Cntl")= para_cntl comobj.parameters("@Dir_Nm")= Dir_Name comobj.parameters("@File_Type")= "SSI" If (Para_Cntl = 1)Then comObj.Execute() Else If Para_Cntl = 2 Then Set objRs = comObj.Execute() End If End If
End Sub Sub Update_Process_Flag(P_Flag,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Flag" comObj.commandtype = 4 comObj.parameters.Refresh comObj.parameters("@Process_Flag")= P_Flag comobj.parameters("@Dir_Nm")= Dir_Name comObj.Execute() End Sub Sub Write_Log comObj.CommandText ="dbo.usp_etl_write_log" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Text")= Details Comobj.parameters("@NDC_SSI_IND")= "SSI" Comobj.parameters("@Process_Stage")= "Staging" comObj.Execute() End Sub
Sub Close_Conn Set comObj = Nothing Set objRs = Nothing conObj.Close Set conObj = Nothing Set fso = Nothing Set folderObj = Nothing Set subFolderList = Nothing End Sub
When I migrated this code to SSIS, its not working. How can I achive this functionality in SSIS. Any one pls help me.
Hello!! searching information about how to migrate some date from an old data base (any tipe) from SQL I´v found this: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...)] Does anybody know how does it works and how to use it????I´d like to know because I have to load data from a text file to a SQL Data Base and this seems to be te fastest an easiest way to do it...Thanks!!!!bye!
I have 2 flat files to load into a datamart via SSIS. Need to implement:- 1. How can I prevent loading of same file again? 2. If by chance wrong data has been loaded how can I rollback? Kindlt guide asap as I have to implement these. JigJan
I am trying to load data from one database to another database with in same sql server.Our DBA has created a new db and I am trying to load data in to this new one.But I am getting error in SSIS packages while scheduled as job. The failed job history was not showing much information So I ran the package dirently to get more info on error message.
I was able to transfer around 5 to 6 tables . But I am unable to transfer the final 2 tables. I am thinking its a space issue. Appreciate if somebidy can explain what the real problem is ?
Below is the error information I got in SSIS
[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available. ".
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
I have a text file to import where there are three file types: a header which has info about who sent the file and begins with 'H', detail records that begins with D and a trailer record that begins with T and just has the record count following that. The fields are delimited by '*'. H, D and T records each contain a different number of fields. I suspect that what I should do is to split this file into three separate files. I tried to do this with SSIS but ran into problems. If I make the output a file destination, it won't let me use that output as input for the next process. There are no arrows I can grab onto to link to the next transform.
This is my first SSIS package although I made hundreds of DTS packages a few years ago. I can't figure this out in DTS either.
This sounds like it should be an EASY thing to do.
Each 01 record type has the records after it associated to it until the next 01 appears, so TestStuff would have TestStuff 2,3 related to it while TestStuff 4,5 belong together. In the example the 888 in the 01 record is the key to the group, but it does not appear in the following lines.
The problem is that each record type has different line formats, columns, etc, so they must be parsed differently. I have created a conditional branch on the first two characters, and written each record type out to a seperate flat file for that type, so that they can be imported again and parsed with the Flat File Source, but I am unsure how to relate them again. I tried appending the 888 to the other lines before they were written out, but I can't find a way to share the variable across the conditional split branches using a script component.
Does anyone have an idea how I could parse these files and keep the relationship intact?
Is there a way to tell the flat file wizard to use a different map based on certain characters?
Is there a way to share a variable across the different braches of a conditional split.
Ok, we have built a data mart using SSIS etc...for transformations and loading.
Our biggest single problem we have currently is loading data from an Oracle server to our SQL server. Some tables from oracle run fine when retrieving the data but there is one particular table that just doesn't load fast enough (9 million records take over 12 hours). It seems that we are idling alot and its not always running.
I am considering whether SSIS can be used to deal with a formatted report that consists of a number of pages delimited by a control character with rows deleimited by CRLF. There are a number of header records that will need to be removed on each page and each line consists of data in fixed width columns.
The files will be fed to us on a regular basis so we will require an automated solution
Can I use one of the import objects available within SSIS directly to deal with this kind of file or will I have to bite the bullet and start coding a solution using C# or vb.net ?
I am so new to SQL Server 2005 and just studying. Saying that... We use SQL Server 2005 Express edition. Some one sent me a file (info.mdb) and asked me to load the data in this file in to a table called Products and also asked me to load in another table (ProdCat) where id = 'X05'. So being not knowing anyting regarding data loading etc, how should I do this and proceed? The .mdb means its a Access database file? If that is the case, I dont have Access in my machine and what should I do?
.If record from Detail (Spectrum table) is null then do insert the record into Spectrum table set status_flag to 'A' for active else do update the record (replace all old values with new values) set status_flag to 'A' for active end-if
· If record from Master (Staging table) is null then do soft delete set status_flag to 'D' for delete end-if
i am performing the ETL on the as400 db2 database using ms- dts,ssis.
i have built the connection b/w as400 and source to extract data from as400 to staging means in dataflow . when i have built the oledb connction for loading data to destination as oledb destination.then it will connct successfully to the db2 as destination but when execute the task then it not load data , and give provider error.
Designing a solution for loading data into SQL destination from a single 5/10 GB flat file? If yes, what kind of performance measures you have taken while designing the solution ?
