Hi All,
New to the forums, so, hello.
I want to import data from multiple XML files into SQL 2005 using SSIS. I know how to set up a flat data source etc... but am unsure how I would go about importing from multiple files.
Anyone got any ideas?
Thanks,
Drammy
Hi,
I have an XML datasource that has multiple nodes at the same level. I need to retrieve contents of two nodes in a dataset. If I have an hierarchy, then I am able to get the information from two nodes. In the current data source, both the nodes are at the same level, without any relationship. I tried the following Query but it returns just the first node.
Please help me.
TIA.
Ashish
Query
<Query>
<ElementPath> root {}/ ReportDoc/ ResourceHeader/ ResourceUsage </ElementPath>
</Query>
XML Datasource
<?xml version="1.0" standalone="yes"?>
<root>
<xs:schema id="ReportDoc" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="ReportDoc" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="ResourceHeader">
<xs:complexType>
<xs:sequence>
<xs:element name="CompanyName" minOccurs="0" msdata:Ordinal="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="StrtDt1" type="xs:dateTime" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="StrtDt2" type="xs:dateTime" minOccurs="0" msdata:Ordinal="2" />
<xs:element name="StrtDt3" type="xs:dateTime" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="StrtDt4" type="xs:dateTime" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="StrtDt5" type="xs:dateTime" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="StrtDt6" type="xs:dateTime" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="StrtDt7" type="xs:dateTime" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="StrtDt8" type="xs:dateTime" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="StrtDt9" type="xs:dateTime" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="StrtDt10" type="xs:dateTime" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="StrtDt11" type="xs:dateTime" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="StrtDt12" type="xs:dateTime" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="WkHrs1" type="xs:decimal" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="WkHrs2" type="xs:decimal" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="WkHrs3" type="xs:decimal" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="WkHrs4" type="xs:decimal" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="WkHrs5" type="xs:decimal" minOccurs="0" msdata:Ordinal="17" />
<xs:element name="WkHrs6" type="xs:decimal" minOccurs="0" msdata:Ordinal="18" />
<xs:element name="WkHrs7" type="xs:decimal" minOccurs="0" msdata:Ordinal="19" />
<xs:element name="WkHrs8" type="xs:decimal" minOccurs="0" msdata:Ordinal="20" />
<xs:element name="WkHrs9" type="xs:decimal" minOccurs="0" msdata:Ordinal="21" />
<xs:element name="WkHrs10" type="xs:decimal" minOccurs="0" msdata:Ordinal="22" />
<xs:element name="WkHrs11" type="xs:decimal" minOccurs="0" msdata:Ordinal="23" />
<xs:element name="WkHrs12" type="xs:decimal" minOccurs="0" msdata:Ordinal="24" />
<xs:element name="TtlWkHrs1" type="xs:decimal" minOccurs="0" msdata:Ordinal="25" />
<xs:element name="TtlWkHrs2" type="xs:decimal" minOccurs="0" msdata:Ordinal="26" />
<xs:element name="TtlWkHrs3" type="xs:decimal" minOccurs="0" msdata:Ordinal="27" />
<xs:element name="TtlWkHrs4" type="xs:decimal" minOccurs="0" msdata:Ordinal="28" />
<xs:element name="TtlWkHrs5" type="xs:decimal" minOccurs="0" msdata:Ordinal="29" />
<xs:element name="TtlWkHrs6" type="xs:decimal" minOccurs="0" msdata:Ordinal="30" />
<xs:element name="TtlWkHrs7" type="xs:decimal" minOccurs="0" msdata:Ordinal="31" />
<xs:element name="TtlWkHrs8" type="xs:decimal" minOccurs="0" msdata:Ordinal="32" />
<xs:element name="TtlWkHrs9" type="xs:decimal" minOccurs="0" msdata:Ordinal="33" />
<xs:element name="TtlWkHrs10" type="xs:decimal" minOccurs="0" msdata:Ordinal="34" />
<xs:element name="TtlWkHrs11" type="xs:decimal" minOccurs="0" msdata:Ordinal="35" />
<xs:element name="TtlWkHrs12" type="xs:decimal" minOccurs="0" msdata:Ordinal="36" />
<xs:element name="UtilHours1" type="xs:decimal" minOccurs="0" msdata:Ordinal="37" />
<xs:element name="UtilHours2" type="xs:decimal" minOccurs="0" msdata:Ordinal="38" />
<xs:element name="UtilHours3" type="xs:decimal" minOccurs="0" msdata:Ordinal="39" />
<xs:element name="UtilHours4" type="xs:decimal" minOccurs="0" msdata:Ordinal="40" />
<xs:element name="UtilHours5" type="xs:decimal" minOccurs="0" msdata:Ordinal="41" />
<xs:element name="UtilHours6" type="xs:decimal" minOccurs="0" msdata:Ordinal="42" />
<xs:element name="UtilHours7" type="xs:decimal" minOccurs="0" msdata:Ordinal="43" />
<xs:element name="UtilHours8" type="xs:decimal" minOccurs="0" msdata:Ordinal="44" />
<xs:element name="UtilHours9" type="xs:decimal" minOccurs="0" msdata:Ordinal="45" />
<xs:element name="UtilHours10" type="xs:decimal" minOccurs="0" msdata:Ordinal="46" />
<xs:element name="UtilHours11" type="xs:decimal" minOccurs="0" msdata:Ordinal="47" />
<xs:element name="UtilHours12" type="xs:decimal" minOccurs="0" msdata:Ordinal="48" />
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="ResourceUsage">
<xs:complexType>
<xs:sequence>
<xs:element name="CompanyName" minOccurs="0" msdata:Ordinal="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SiteName" minOccurs="0" msdata:Ordinal="1">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OrganizationID" minOccurs="0" msdata:Ordinal="2">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OrganizationName" minOccurs="0" msdata:Ordinal="3">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ResourceCode" minOccurs="0" msdata:Ordinal="4">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ResourceDesc" minOccurs="0" msdata:Ordinal="5">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Prd1" type="xs:decimal" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="Prd2" type="xs:decimal" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="Prd3" type="xs:decimal" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="Prd4" type="xs:decimal" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="Prd5" type="xs:decimal" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="Prd6" type="xs:decimal" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="Prd7" type="xs:decimal" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="Prd8" type="xs:decimal" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="Prd9" type="xs:decimal" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="Prd10" type="xs:decimal" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="Prd11" type="xs:decimal" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="Prd12" type="xs:decimal" minOccurs="0" msdata:Ordinal="17" />
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="ReportSummary">
<xs:complexType>
<xs:sequence>
<xs:element name="PrdId" type="xs:int" minOccurs="0" msdata:Ordinal="0" />
<xs:element name="StrtDt" type="xs:dateTime" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="EndDate" type="xs:dateTime" minOccurs="0" msdata:Ordinal="2" />
<xs:element name="WkHrs" type="xs:decimal" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="TtlWkHrs" type="xs:decimal" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="UtilHours" type="xs:decimal" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="GraphUtilized" type="xs:decimal" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="GraphAvailable" type="xs:decimal" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="GraphOverUtilized" type="xs:decimal" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="SiteURN" minOccurs="0" msdata:Ordinal="9">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="128" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<ReportDoc>
<ResourceHeader>
<CompanyName>EPIS Software - US</CompanyName>
<StrtDt1>2004-11-07T00:00:00.0000000-06:00</StrtDt1>
<StrtDt2>2004-11-14T00:00:00.0000000-06:00</StrtDt2>
<StrtDt3>2004-11-21T00:00:00.0000000-06:00</StrtDt3>
<StrtDt4>2004-11-28T00:00:00.0000000-06:00</StrtDt4>
<StrtDt5>2004-12-05T00:00:00.0000000-06:00</StrtDt5>
<StrtDt6>2004-12-12T00:00:00.0000000-06:00</StrtDt6>
<StrtDt7>2004-12-19T00:00:00.0000000-06:00</StrtDt7>
<StrtDt8>2004-12-26T00:00:00.0000000-06:00</StrtDt8>
<StrtDt9>2005-01-02T00:00:00.0000000-06:00</StrtDt9>
<StrtDt10>2005-01-09T00:00:00.0000000-06:00</StrtDt10>
<StrtDt11>2005-01-16T00:00:00.0000000-06:00</StrtDt11>
<StrtDt12>2005-01-23T00:00:00.0000000-06:00</StrtDt12>
<WkHrs1>0</WkHrs1>
<WkHrs2>0</WkHrs2>
<WkHrs3>0</WkHrs3>
<WkHrs4>0</WkHrs4>
<WkHrs5>0</WkHrs5>
<WkHrs6>0</WkHrs6>
<WkHrs7>0</WkHrs7>
<WkHrs8>0</WkHrs8>
<WkHrs9>32</WkHrs9>
<WkHrs10>40</WkHrs10>
<WkHrs11>40</WkHrs11>
<WkHrs12>40</WkHrs12>
<TtlWkHrs1>0</TtlWkHrs1>
<TtlWkHrs2>0</TtlWkHrs2>
<TtlWkHrs3>0</TtlWkHrs3>
<TtlWkHrs4>0</TtlWkHrs4>
<TtlWkHrs5>0</TtlWkHrs5>
<TtlWkHrs6>0</TtlWkHrs6>
<TtlWkHrs7>0</TtlWkHrs7>
<TtlWkHrs8>0</TtlWkHrs8>
<TtlWkHrs9>32</TtlWkHrs9>
<TtlWkHrs10>40</TtlWkHrs10>
<TtlWkHrs11>40</TtlWkHrs11>
<TtlWkHrs12>40</TtlWkHrs12>
<UtilHours1>0</UtilHours1>
<UtilHours2>0</UtilHours2>
<UtilHours3>0</UtilHours3>
<UtilHours4>0</UtilHours4>
<UtilHours5>0</UtilHours5>
<UtilHours6>0</UtilHours6>
<UtilHours7>0</UtilHours7>
<UtilHours8>0</UtilHours8>
<UtilHours9>0</UtilHours9>
<UtilHours10>0</UtilHours10>
<UtilHours11>0</UtilHours11>
<UtilHours12>0</UtilHours12>
</ResourceHeader>
<ResourceUsage>
<CompanyName>EPIS Software - US</CompanyName>
<SiteName>US</SiteName>
<OrganizationID>10010001104</OrganizationID>
<OrganizationName>Executives - Irvine</OrganizationName>
<ResourceCode>EHILL</ResourceCode>
<ResourceDesc>Eu Hll</ResourceDesc>
<Prd1>0</Prd1>
<Prd2>0</Prd2>
<Prd3>0</Prd3>
<Prd4>0</Prd4>
<Prd5>0</Prd5>
<Prd6>0</Prd6>
<Prd7>0</Prd7>
<Prd8>0</Prd8>
<Prd9>0</Prd9>
<Prd10>0</Prd10>
<Prd11>0</Prd11>
<Prd12>0</Prd12>
</ResourceUsage>
<ReportSummary>
<CompanyName>EPIS Software - US</CompanyName>
<PrdId>1</PrdId>
<StrtDt>2004-11-07T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-14T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>2</PrdId>
<StrtDt>2004-11-14T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-21T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>3</PrdId>
<StrtDt>2004-11-21T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-28T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>4</PrdId>
<StrtDt>2004-11-28T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-05T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>5</PrdId>
<StrtDt>2004-12-05T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-12T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>6</PrdId>
<StrtDt>2004-12-12T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-19T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>7</PrdId>
<StrtDt>2004-12-19T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-26T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>8</PrdId>
<StrtDt>2004-12-26T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-02T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>9</PrdId>
<StrtDt>2005-01-02T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-09T00:00:00.0000000-06:00</EndDate>
<WkHrs>32</WkHrs>
<TtlWkHrs>32</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>10</PrdId>
<StrtDt>2005-01-09T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-16T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>11</PrdId>
<StrtDt>2005-01-16T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-23T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>12</PrdId>
<StrtDt>2005-01-23T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-30T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
</ReportDoc>
</root>
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B).  Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies View RelatedI need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
I used the data export wizard to export a single table to a single flat file (multiple wasn't allowed). I saved the package as a *.dtsx file which I'm attempting to edit to add the additional tables.
Creating additional sources is fairly easy copy of the first source and change to the table name.
I've tried copying the destination connection and changing to a new text file, but can't get past having to add each column manually to the new destination.
How can I duplicate the mapping that must be taking place in the wizard in the *.dtsx editing environment?
This seems like a simple / common task, but I've been unable to find a solution.
Thanks, Richard
Hi,
I have searched but not found quite the best way to look at this so far..
I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.
In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.
Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?
Just wondering if there is a neater way of doing this??
It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.
Hope that explains ok and thanks in advance.
Mike
I am trying to write (my first, unfortunatly) DTS, and am having some problems.
I need to be able to import multiple flatfiles (all in the same format, just with different schema), each one going into a different table. I have written an application to call my DTS, sending it variables for the tablename and the filename. This works fine when I test it on a single flatfile.
My problem is, the Tranformation object does not reset after each DTS call, so I get "Column does not exist" errors after the first successful import. I can go into the DTS Manager and reset the Transformation options, but that would defeat the purpose of automation. Is there anyway to reset, or another technique, the Transformation object so that it will continuosly work on files that use different schema?
I am very new at DTS, so please consider me "ignorant" when replying.
Thanks in advance.
- Jordan
I have multiple .sql files, exe each one manually is a pain, so how do you run multiple .sql files all at once? Beside creating a batch file, are there t-sql commands that could execute .sql files?
Thanks!
I have a rather large sale transaction DB. Basic header, and detail tables. I am providing a third party company with daily sales information, and I need to give them back data from about 8 or 9 months ago. I currently have a DTS package that gets sales for the current day, but since I have to go back, I have to manually edit the query in the DTS package, and change the date range...UNLESS ...
Blah, blah, blah. The problem is that they can only take the data in Daily files. So, there would be ONE file for each day. I really don't need to be manually running these jobs, so I'm wondering if someone could point me to a way of writing a package (maybe ActiveX, not sure) that would run through a loop, basically, of dates, and create a seperate file for each day. Versus having to edit a generic DTS package, and changing the date range 350 times...
I need to restore a database that has a bak file and many log files. Is there a way I can do this with one recovery step?
Or do I need to use a different restore object for each file?
MTmace
Using an expression to set the log filename to include the date and time results in 3 log files being created.
Ummm. Why? I only ran the package once. Is SSIS not sharing my log file connection among the different components?
On first thought my workaround would involve using a script task to "set" the log file name to a variable and use an expression to set the log connection to the variable. But the problem with that is that logging starts BEFORE the script task is run...
I have a job with a single step that executes a stored procedure that performs the following steps:
1. Checks for the existance of a file A in a folder A
2. If it exists,
a. executes the cmdshell to run a DTS package to drop a table, recreate it and load the data in the file A to table X
b. runs other stored procedures that use the data in table X to create other tables Y and Z
c. executes the cndshell to remove and rename the file A from Folder A into Folder B
What I'd like to do is use this same stored procedure if possible, but create a job or another store procedure that would loop thru and process multiple files in Folder A instead of just one.
Any suggestions would be greatly appreciated
Hi,
I have about 300-400 XML files I want to load in my SQL database (2005). The following code will load one (1) file. How do i do a mulitple collections?
INSERT INTO MEL (DATA) SELECT * FROM OPENROWSET (BULK'C:TempCHAPTER1.xml', SINGLE_BLOB) AS TEMP Thanks,
Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who's properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?
View 11 Replies View RelatedIs it possible to take a text file that contains multiple record types through the Data Transformation Service in MS SQL 7.0 and load each different record type into a seperate table?
Thanks in advance.
We have a large Database (91 GB) that is currently in one large data file. Now that we have muliple disk arrays I can split that up on I would like to have a couple data files. My question is, what is the best way to split this up? Should I keep one primary file group and just create another file, or should I create a file group for indexes and put those on that? This database is used for reporting only so it doesn't really have any writes being done on it.
Thanks much.
I have 8GB of text files which are basically log files from the past few years.
There is 24 text files per directory which are labeled for every day (so they are not all in 1 folder).
It would make reading them much easier if I could import them to SQL but I only seem to be able to import 1 at a time? (with the wizards :eek: )
Surely there is a way to mass import without all the costly applications that google searches give me?
cheers :P
When having multiple files for log, do they get filled one after the other (like Oracle) or proportionately fill?
------------------------
I think, therefore I am - Rene Descartes
I have a script which imports the contents of a csv file from our CRM system and updates a table in my database. This works OK but the problems I have are that a) sometimes there is more than one file in the folder, and b) that I wish to move any csv files that have been imported into an archive folder. The csv files arrive with a time/datestamp and I currently rename them manually to FREXPORT before importing (the name is in the format FREXPORT_20141101_1217.csv).How do I:
1) get it to process the file without me having to manually rename the file(s) each time,
2) if there is more than 1 file in the folder process all the files and 3)move the correctly processed files to an archive folder which is: importarchive?
Ultimately, I would like the script to be run as a scheduled job, so it also has to deal with the fact that sometimes there will be no files to import too.
[code]create table #import
(worknumber nvarchar(12), date_done smalldatetime)
BULK
Insert #import
from 'fork04-hq-dc01dataimportFREXPORT.csv'
[code]....
Hi i have a FTP task on my SSIS package where i want to select 3 files from a directory, this directory already contains other files but i only want 3 of them how do i only pull down the 3 files from the FTP site i can't seem to find a option on the FTP task to select what files i want from the direcroty.
View 3 Replies View RelatedI have over three hundred text files that I need to import to SQLServer.Each is in the exact same format.I want to import tham as seperate tables.Is there any way to do it in one process?Regards,Ciarán
View 5 Replies View RelatedHi,
I have a situation where every morning files are downloaded from an ftp site.
Problem is, I don't know beforehand how many files there will be. Usually it's one or two, but might be more. All files need to be loaded in the database.
How can I account for there multiple files, whose number I do not know beforehand? That is, in the file connection manager, how do I tell it which files to load?
Help.
Is there any control flow task or any task in SSIS that is able to get 50 XML files
from a directory on a server and insert them into a table/column of xml datatype
in a sql server database?
I lnow I can use the foreachloop container and specify the directory path to pick up
the xml files but what do you do after that?
I also know of the OPENROWSET Function but that does 1 file at a time only!
??
Hi,
When do we need to have multiple log files for one Database.
What performance counters should I measure before deciding to go for multiple log files.
Any comments / suggestions are welcome.
Thanks,
Loonysan
Hi everyone,
How to change dynamically the connection properties for a DTSX?
Imagine that you launch a SSIS and under a several rules the same connection is reused with four or five different databases.
I know that I can attach a configuration file or more than one but how to tell to SSIS which use in every moment?
Thanks in advance and regards,
I have 60 XML files in the following directory, how do I loop through each one to insert into a table like the code below only does 1 file.
How do I store each filename in a variable and the concatenate it to the following code?
DECLARE @XML XML
SELECT @XML = CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK '\SQLSRV1ConvergCust_21012008.xml' , SINGLE_BLOB) AS x
INSERT dbo.Test.XML (XMLDoc)
SELECT @XML
I have the folliwng code which inserts 1 file into a table, but how to do with 10 files without running this code manually 10 times?
DECLARE @XML XML
SELECT @XML = CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK '\WS51510C$XMLDataCust_20080101.xml', SINGLE_BLOB) AS x
INSERT dbo.XMLDocument (XMLDoc)
SELECT @XML
Is there a way to get more than one file with a single ftp task in SQL 2005??
I need to get 5 files from one server. They are in two different directories is that makes any difference. Right now I have a separate task for each but would like to have one task if possible.
Thanks
I have a number of script files that create various objects in my database, and to create a new DB from scratch I need to run them all in a certain sequence.
Is it possible to create a master script file that calls the others in the desired sequence?
P.S. I am using SQL Server Management Studio Express to edit and run the scripts.
Ok - dont' throw rotten food at me for asking this question...
Is there any advantage (faster I/O) to creating multiple mdb files on the same RAID 5 for the same database?
In other words, database ABC is 100 gb in size and has one primary file on H:. If I create another (or 3 or 6) secondary files for the mdb, would there be an appreciable performance gain? Same with log file?
Thanks in advance.
DataGeek