Need help as i am new to SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Need help as i am new to SSIS

Author

Topic

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-24 : 08:58:45

Hi All,

Very Good Morning, Afternoon & Evening,

i am very new to SSIS and currently getting opportunity using SSIS using 2008 R2 version.

HELP ITEM

1. currently i have Multiple XML file and i need to consolidate , Merge , Append the data - CLose to 100- xml File i have XML Schema for the same with me will share that at the end of the request.

2. here i having difficulty in getting the Unique ID properly "ID929572783" that is not coming properly is generating own id like random number where i cannot map it back to my data.

3. tried to change the data type for the Unique id from "DT_WSTR" to "DT_STR" no go so tried with other data type "DT_GUID" still no go it generating the own id.

Tried this possible solution to merge the data

a.File Name - String - folder path
b.File Extension - String - *.xml
c.Full Path Name - output data path
1. For Each Loop Container
2. double clicked on "FELC" properties
3. Under Collection For Each File Enumerator
4. Folder Name - provided the path where all the XML is stored
5. files - *.xml
6. Retrieve File Name - Fully Qualify
7. click on Variable Mapping
8. Variables = User :: Full Path - Index = 0
9. Click on ok
10. Dragged & Dropped Data Flow Task (DFT) double clicked
11. Dragged & Dropped the XML Source, provided the Source xml file
12. created the Schema and clicked ok
13. right click XML Source and click on ADvanced Editor
14. Input and Output Properties select the 11 tables / elements and changed the data type for all the Text files to "DT_NTEXT" as this was done by some other person where is he not available or not reachable
15. clicked on ok
16.tried to run the FELC and it populated the data in the respective table but it is not moving from one file to another file it is only doing for the same Session 1 it has to go till Session 100.

need help in consolidating the XML & Session Id to be actual data not auto generated.

XML SCHEMA

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Report">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Session">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Visitor">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ip" type="xs:string" />
<xs:element minOccurs="0" name="agent" type="xs:string" />
<xs:element minOccurs="0" name="host" type="xs:string" />
<xs:element minOccurs="0" name="chatReferer" type="xs:string" />
<xs:element minOccurs="0" name="GeoInfo">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="geoCity" type="xs:string" />
<xs:element minOccurs="0" name="geoConType" type="xs:string" />
<xs:element minOccurs="0" name="geoCountry" type="xs:string" />
<xs:element minOccurs="0" name="geoIP" type="xs:string" />
<xs:element minOccurs="0" name="geoISP" type="xs:string" />
<xs:element minOccurs="0" name="geoLat" type="xs:decimal" />
<xs:element minOccurs="0" name="geoLong" type="xs:decimal" />
<xs:element minOccurs="0" name="geoOrg" type="xs:string" />
<xs:element minOccurs="0" name="geoPost" type="xs:string" />
<xs:element minOccurs="0" name="geoReg" type="xs:string" />
<xs:element minOccurs="0" name="geoTimeZone" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedLong" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Chat">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="line">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="URL" type="xs:string" />
<xs:element minOccurs="0" name="HTML" type="xs:string" />
<xs:element minOccurs="0" name="Text" type="xs:string" />
</xs:sequence>
<xs:attribute name="by" type="xs:string" use="optional" />
<xs:attribute name="time" type="xs:dateTime" use="optional" />
<xs:attribute name="repId" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="start_time" type="xs:dateTime" use="optional" />
<xs:attribute name="end_time" type="xs:dateTime" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="VarValues">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="varValue">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="id" type="xs:string" use="optional" />
<xs:attribute name="source" type="xs:string" use="optional" />
<xs:attribute name="sourceName" type="xs:string" use="optional" />
<xs:attribute name="time" type="xs:dateTime" use="optional" />
<xs:attribute name="name" type="xs:string" use="optional" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Reps">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Rep">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="id" type="xs:string" use="optional" />
<xs:attribute name="repName" type="xs:string" use="optional" />
<xs:attribute name="startTime" type="xs:dateTime" use="optional" />
<xs:attribute name="endTime" type="xs:dateTime" use="optional" />
<xs:attribute name="order" type="xs:unsignedByte" use="optional" />
<xs:attribute name="employeeId" type="xs:string" use="optional" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
<xs:attribute name="realTimeID" type="xs:unsignedLong" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Variables">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="var">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="title" type="xs:string" />
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
<xs:attribute name="scope" type="xs:string" use="optional" />
<xs:attribute name="name" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="account" type="xs:unsignedInt" use="optional" />
<xs:attribute name="start_time" type="xs:dateTime" use="optional" />
<xs:attribute name="end_time" type="xs:dateTime" use="optional" />
<xs:attribute name="user" type="xs:string" use="optional" />
<xs:attribute name="limit" type="xs:unsignedShort" use="optional" />
<xs:attribute name="more_sessions" type="xs:boolean" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>

awaiting for your reply / please call me

Regards,
Karthik Shankaran (KS)
99400 74724

Regards,
Karthik Shankaran (KS)
+91-9940074724

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-24 : 09:55:14

I don't know much about SSIS tasks. The following link might help you
http://objectmix.com/xml-soap/85566-combining-xml-files-using-ssis-t-sql-etc.html

--
Chandu

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-24 : 10:08:08

Hi Chandu,

thanks for your prompt response, i have done the For Each Loop Container but i might be missing something in my steps . i dont know what i missed.it is asking to get the information from BOL i dont know what is that :( . i am currently using 2008 R2 version. please help. if you can provide me your contact number will call and explain you my scenario so that it might give you more idea on what the issue is.

Regards
KS
99400 74724

Regards,
Karthik Shankaran (KS)
+91-9940074724

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 12:41:04

As I understand the only think you've missed here is to set an expression for xml source property to variable which gets the xml path value inside ForEachLoop. This will make sure the path gets changed automatically to each file's path and corresponding XML file being fetched by XML source and doing data transfer using data flow task to your table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 00:25:44

Hi Visakh

1. XML Source double clicked
2. Data Access Mode - changed from "XML File Location" to "XML File From Variables"
3. as created the Variables - Full Path
4. clicked ok

still not working

Regards,
Karthik Shankaran (KS)
+91-9940074724

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 00:50:23

quote:
Originally posted by karmeeabi

Hi Visakh

1. XML Source double clicked
2. Data Access Mode - changed from "XML File Location" to "XML File From Variables"
3. as created the Variables - Full Path
4. clicked ok

still not working

Regards,
Karthik Shankaran (KS)
+91-9940074724

Put a break point in preexecute event of data flow task and check if variable is correctly getting XML file path

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 04:37:32

Hi Visakh,

that fixed my Issue, now it is new issue that got created. as i have told you earlier it generate automatic id now it is repeating the same for all the XML file as unique i dont know how to get out of that. below mentioned are the steps i have followed.

1. Defined 2 variables
a. FullPath , String , Folder name where the XML are stored
b. FileExtension , String, .xml
2. Dragged & Dropped the FELC
3. Double Click on it & clicked on "Collection"
4. Under Enumerator Configuration - Provided the File Path Name where the XML are stored & for Files "*.xml" and selected "Fully Qualified"
5. clicked on Variable Mappings - User::FullPath - Index = 0 and clicked ok
6. Dragged & Dropped the DFT & Double Clicked
7. Dragged & Dropped the XML Source, Dervied column & Destination Database
8. XML SOurce right clicked on "Show Advance Editor" and "Input and Output Properties" changed for some of the Input & Output Child Node data type changed and clicked ok
9.Derived Column double clicked and added a new column as "File Name" called the Variable " Full Path" and clicked ok
10. Destination Database Connection Manager select the the destination server & database- double clicked and selected "Table or View - fast load" , unchecked Table Lock & CHeck Constraints & clicked on new, changed the Name while creating the table and clicked on Mappings - mapped the value appropriately and clicked on Error Output and clicked ok
11. like this i have done for 11 elements / table which is available in the XML
12. tried to ran / execute the SSIS it ran but got an error message for one of the 11 elements / table "Child Node - URL" Truncation error .

i dont know how to rectify this issue + i also need to one more help i need to create a composite key i have never done this - it should be based on the File Name , Session Id & ID to get the actual Session ID for all the cases

could you please help me to fix this

once again that for the help

Regards,
Karthik Shankaran (KS)
+91-9940074724

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 04:42:55

This is the error message i am getting.

[XML Source [1]] Error: The "component "XML Source" (1)" failed because truncation occurred, and the truncation row disposition on "output column "URL" (151)" at "output "line" (140)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[XML Source [1]] Error: The component "XML Source" (1) was unable to process the XML data. Pipeline component has returned HRESULT error code 0xC02090FA from a method call.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "XML Source" (1) returned error code 0xC02092AF. 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. There may be error messages posted before this with more information about the failure.

now i have changed the data type for line_URL to DT_Text and tried to changed the same in the table as well to check if that is fixing my issue will keep you posted on the same

Regards,
Karthik Shankaran (KS)
+91-9940074724

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 04:46:19

it generate automatic id now it is repeating the same for all the XML file as unique i dont know how to get out of that

how are you generating this id? using t-sql code?

tried to ran / execute the SSIS it ran but got an error message for one of the 11 elements / table "Child Node - URL" Truncation error .
The error may be because one of destination table fields will not have enough length to hold value from xml. compare datatypes between source and destination. Also check in derived column where you are applying any expression over data and make sure you're not causing the original data precision to be lost due to your expression which may also cause this error

i also need to one more help i need to create a composite key i have never done this - it should be based on the File Name , Session Id & ID to get the actual Session ID for all the cases
do you mean setting composite primary key? or using an expression to concat all values to form surrogate key?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 04:52:51

quote:
Originally posted by karmeeabi

This is the error message i am getting.

[XML Source [1]] Error: The "component "XML Source" (1)" failed because truncation occurred, and the truncation row disposition on "output column "URL" (151)" at "output "line" (140)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[XML Source [1]] Error: The component "XML Source" (1) was unable to process the XML data. Pipeline component has returned HRESULT error code 0xC02090FA from a method call.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "XML Source" (1) returned error code 0xC02092AF. 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. There may be error messages posted before this with more information about the failure.

now i have changed the data type for line_URL to DT_Text and tried to changed the same in the table as well to check if that is fixing my issue will keep you posted on the same

Regards,
Karthik Shankaran (KS)
+91-9940074724

Whats the datatype it assumes while taking the column from XML source? Have a look at data flow path from XML SOurce (duble click on it) and see the metadata definition for URL field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 05:07:27

getting this error message : [Line_Raw_Data [408]] Warning: The external columns for component "Line_Raw_Data" (408) are out of synchronization with the data source columns. The external column "line_URL" needs to be updated.

actually it assumed DT_WSTR 255,0 i have changed that to DT_NTEXT after which is consolidating / merging / append

here are the error that is showing while doing the consolidation process

1. [Line_Raw_Data [408]] Warning: The external columns for component "Line_Raw_Data" (408) are out of synchronization with the data source columns. The external column "line_URL" needs to be updated.

2. [Line_Raw_Data [408]] Warning: The external columns for component "Line_Raw_Data" (408) are out of synchronization with the data source columns. The external column "line_URL" needs to be updated.

3. [SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.

4. [Line_Raw_Data [408]] Warning: The external columns for component "Line_Raw_Data" (408) are out of synchronization with the data source columns. The external column "line_URL" needs to be updated.

5. [Line_Raw_Data [408]] Warning: The external columns for component "Line_Raw_Data" (408) are out of synchronization with the data source columns. The external column "line_URL" needs to be updated.

6. [Line_Raw_Data [408]] Warning: The external columns for component "Line_Raw_Data" (408) are out of synchronization with the data source columns. The external column "line_URL" needs to be updated.

it seems to be the same error while loading the data

can i have you contact number so that i can talk to you and see if i can get my issue resolved

Regards,
Karthik Shankaran (KS)
+91-9940074724

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 05:12:15

it ran successfully now i need to your help in getting the value

1. Session ID -Autogenerated by the sql itself in all the table
2. ID - Actual Session id is available in the Session_Raw_Data Table with Session_ID also available with File Name which will help me to map the correct id

my solution for this to concatenate " Session ID + " " + File Name" should be added in all the table so that i can lookup using that and put the correct ID which is Session ID

Regards,
Karthik Shankaran (KS)
+91-9940074724

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 05:27:03

thats because the datatype you selected currently is imcompatible with what it set in metadata initially. double click on the xml source and it will provide you with option to fix the error automatically. Click ok and it will revert to earlier compatible type. Go to derived column add a cast expression to change column type to what you wanted.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 06:14:19

Hi Visakh,

can i have you mobile # or can you please give me a missed call so that i can call you and fix this issue if you dont mind please

Regards,
Karthik Shankaran (KS)
+91-9940074724

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 06:18:44

quote:
Originally posted by karmeeabi

Hi Visakh,

can i have you mobile # or can you please give me a missed call so that i can call you and fix this issue if you dont mind please

Regards,
Karthik Shankaran (KS)
+91-9940074724

Post your question here and I'll take a look when I've sometime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 08:24:52

Hi Visakh,

i have completed the XML - Consolidation / Merge / Append. now i have issue in getting the right number.

1. Session ID which is present in 4 tables (Chat , REps ,Varvalues & Visitor) from the XML - which seems to be autogenerated or something which i dont know

2. i have a table called Session where i have the Session Id - looks like the above mentioned "1". but i have a column called ID which is unique Session ID - now i need to map that with the above tables .

thinking of this Possible Solution

Simple merge with the Reps with Session , Chat with Session , Visitor with Session & Varvalues with session

or is there any other solution to get that data

example of the data

Auto generated Session Id this will be in all xml file which is downloaded same number repeats + i have a derived column - File Name which will let me know which XML file is that from
1 in all 5 tables
92 in all 5 tables
97 in all 5 tables
199 in all 5 tables
208 in all 5 tables

Respective ACtual Session ID from Session Table - ID but this will be unique with the File Name matched using Derived Column

Table 1

ID929572783
ID929575783
ID929572783
ID929582783
ID923572483

Table 2

ID922572783
ID924575583
ID929370773
ID922585788
ID913372583

Table 3

ID922572783
ID924355583
ID929890773
ID924885688
ID913336783

Table 4

ID928492783
ID924355583
ID929893983
ID924839858
ID913209633

Table 5

ID994952783
ID924499883
ID929809083
ID924390958
ID994059633

can you please guide me to the right direction

Regards,
Karthik Shankaran (KS)
+91-9940074724

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 09:44:46

Hi,

i have written this in SQL but i am not too sure how to do that same in SSIS.

select * into chat_1 from chat_raw_data full join session_raw_data
on (session_raw_data.session_id=chat_raw_data.session_id1) and (session_raw_data.[file name]=chat_raw_data.[file name1])

SSIS

1. i have tried in ssis
2. Drag & Drop the Database Source - 2
3. right click show advance editor - is sorted = "True"
4. Input and Output Properties - select the column as 1
5. Input and Output Properties - select the column as 2 **** Note i am trying this but unable to get it
6. Merge Join Full Outer Join and clicked ok
7. Destination Database created
8. clicked ok

i am able to do that with out option 5. i dont know how to go with that.

Regards,
Karthik Shankaran (KS)
+91-9940074724

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 10:27:09

Sorry I'm not getting your requirement. Are you trying to map values in four tables against that in Session table? Whats was the deal with the SessionID value from XML? I didnt get it.
Can you please explain with an example? what do you mean by below?


Simple merge with the Reps with Session , Chat with Session , Visitor with Session & Varvalues with session

or is there any other solution to get that data

example of the data

Auto generated Session Id this will be in all xml file which is downloaded same number repeats + i have a derived column - File Name which will let me know which XML file is that from
1 in all 5 tables
92 in all 5 tables
97 in all 5 tables
199 in all 5 tables
208 in all 5 tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 13:45:06

now i have a different issue. i completed all the work merged the XML in one. i save my SSIS work and closed it
after i tried to open i am unable to execute it i dont know what the problem is. please help me i have done all my work.

Regards,
Karthik Shankaran (KS)
+91-9940074724

karmeeabi
Starting Member

18 Posts

Posted - 2013-06-25 : 13:46:32

i did save it in my desktop & share folder in my office server. both it is not working unable to execute the file not getting any error message please help me Visakh

Regards,
Karthik Shankaran (KS)
+91-9940074724

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 02:29:40

Without giving any info on the error, how can anybody guess what's the issue. Atleast post the error you're getting.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources