I have created a ssis package which uses an xml source and and ole db destination.
It basically transfers the data from an xml file on my machine into a table in the sql server database.
This .xml file is in c:DownloadsV5 CDS Composites1209558837833.xml
This is the file that has been placed here automatically by a third party tool.
When my ssis package imports the xml data into sql server, I then move this .xml file into another folder on my machine.
So every day there is a new .xml file which has the same name except the numbers are different as follows:
Question:
In the xml source, xml location what do I put there to only look for the xml file which starts with V5 CDS Composites
is it something like V5 CDS Composites*.xml? - notice the asterisk
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.Â
Hi, I want to transform an xml flow to an html flow. For this, I create an XmlDataDocument, I add on it, all that I want, and I store it in a file (in a dataflow task). After that, in an xml task, I set the input properties like that: Operation type: xslt SourceType: File connection Source: ras.xml
All works fine. But now, I want to use a variable to store my xml data. So, instead of storing my data in ras.xml, I store it in a variable like that:
Dim v As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90 Me.VariableDispenser.LockOneForWrite("RAS", v) v("RAS").Value = doc.OuterXml v.Unlock()
and in the xml task, I set configuration like that:
But when I execute, I got this following exception:
Error: 0xC002F304 at Format HTML Mail, XML Task: An error occurred with the following error message: "Root element is missing.". Error: 0xC002928F at Format HTML Mail, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Furthermore, when I want to debug it, in replacing the xml task, by a script task, I can see that in my RAS variable, I get the xml data which is well formed.
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
I am having a huge xml file with nested section. i also have a xsd file for that xml. i have a destination table where the data from the xml should be loaded into. i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination. on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files. also i would like to know any other better way of getting the data from xml source without using the joins.
I am trying to create a program that transfers tables to flat files. At this point in time, I have suceeded in created one that creates delimited files.
However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.
Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.
I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.
, Hi In this code how can I create a new data source and new data source view and model and structure that it run dynamic. In this code I have a lot of errors, that they are about server and database don€™t have in current code, In this code, first I should definition server or no?
How can I create data source and data source view and model and structure? Please say code of that, and guide me. databasename and srv is unknown. Do I add other reference with analysis services? Please explain about these codes: ************************************************************************ 1) RelationalDataSource dsNew = new RelationalDataSource( datasourceName, Utils.GetSyntacticallyValidID( datasourceName, typeof(RelationalDataSource)));
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
hi everybody, i want to create data source and data source view for data mining, with using C Sharp. i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully, and have i build data source and data source view, how to do it.?
Today I was making a few reports. When I tested the reports in Visual Studio, they worked great: I got the expected result. But when I deployed the reports to our reportserver the problem started. When I click on the directory in which my reports are deployed, I got my 4 reports. Till now everything worked correct. But when I click on a report to view the results it went wrong. I got an error: "Cannot create a connection to data source 'Live'" (Live is the name of our data source).
We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work. I also have tried it with all the roles assigned on my account, but then it still won't work.
When I modify the data source, and set it to another server en database it works. The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server. Maybe that is the problem?
as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?
how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?
Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?
Hi, I have created a ssis package which uses an xml source and and ole db destination. It basically transfers the data from an xml file on my machine into a table in the sql server database. This .xml file is in c:DownloadsV5 CDS Composites1209558837833.xml This is the file that has been placed here automatically by a third party tool. When my ssis package imports the xml data into sql server, I then move this .xml file into another folder on my machine. So every day there is a new .xml file which has the same name except the numbers are different as follows: Thursday --> V5 CDS Composites1209558837833.xml friday -> V5 CDS Composites65496845732.xml saturday --> V5 CDS Composites1642389243.xml ... ... ...
Question: In the xml source, xml location what do I put there to only look for the xml file which starts with V5 CDS Composites is it something like V5 CDS Composites*.xml? - notice the asterisk Thanks
Why does XML Source in MS VS 2005 Integration Services Data Flow Sources XML Source not pick up the root element?
The problem is the following example: <root createddate="2008-01-01">
<elements> ... </elements</root>
The XML Source picks up everything after the first element which is the root element but it does not seam to pick up the root element where there is a need for the createddate attribute.
<asp:SqlDataSource ID="dsBorrowers" runat="server" SelectCommand="SELECT * FROM [BORROWERS]" ConnectionString="<%$ ConnectionStrings:OxfordLibrary %>"> </asp:SqlDataSource> if i put this code inside source code then i can bind my gridview i want to bind gridview when button is clicked using a sqldatasource control could please tell me the way i can do it
Hello, I am trying to figure out how to hard code a value ("Outage") in one of the Parameter fields.. can someone give me an example of how you would do this? here is the code snippet<asp:SqlDataSource ID="SqlOutage" runat="server" ConnectionString="<%$ ConnectionStrings:EDCCConnectString %>" SelectCommand="OUT_GetDailyDetails" SelectCommandType="StoredProcedure"> <SelectParameters><asp:SessionParameter Name="StartDate" SessionField="StartDate" Type="DateTime" /> <asp:SessionParameter Name="EndDate" SessionField="EndDate" Type="DateTime" /> (Here is what I am trying to hard code in )"Outage" Type="String" /> </SelectParameters> </asp:SqlDataSource>
Can anyone tell me how I can copy store procedures from SQL Server 2000 to Source Safe 6.0? I right-click the store procedure and cop y it but when I got to the visual source safe it will not work. I therefore went through the soruce code control on Tool menu which I cannot see such commands. I do really need your help.
At a high level, this is what I need to do in a stored procedure:
CREATE VIEW TEMP_VIEW AS SELECT * FROM SOURCE_XLS...Sheet1$
-the reason for creating the view is that the number of columns in Sheet1$ is not static. By creating the view, I then can determine with sysobject/syscolumns how many columns I have to process for each row.
DECLARE my_cursor CURSOR FOR SELECT * FROM TEMP_VIEW
OPEN my_cursor DECLARE ? ? FETCH NEXT FROM my_cursor INTO ?? WHILE <etc> DO STUFF WITH EACH COLUMN IN THAT ROW (as noted above, this will never be a static list of columns) END CLOSE my_cursor DEALLOCATE my_cursor
…so, I’m obviously stuck on what I’m going to fetch my_cursor into. Am I limited to scalar types?
…and, inside the WHILE loop, can I refer to the columns by their index (i.e. which column number they are in the result set as opposed to the name)
…or is it back to the drawing board for a completely different approach.
I have a source file name which changes every day , basically the filename like 9500 , the next file would come is 9501 , then next could be anything may be 1000Q. I need the source connection to pick dynamicallly those file name. is their any way i could achieve this in SSIS.
I would not know what is the name of the file i would expect
I am having a problem in visual studio. I have a report written using one data base. Now i want to add another database to the same report but cannot figure out how to do this. They are both shared data sources. I am pretty new to this but i can write reports using one source but cannot figure out how to use more than one data source.
I have an XML data file and an associated XSD file with properly defined datatypes. However, the datatype of all the data elements are always "string" datatype. For example, in my current xml file, all the data elements are of Decimal datatype which is properly defined in XSD file. However, datatype of all the output columns are of string datatype.
i'm using an xml source for my data flow task. sometimes its giving 900 rows. sometimes its giving 2265 rows as output from xml source? have you ever face this problem? i could not understand this issue! (the xml data would be coming every month, same fields so same XSD) i suspect the problem with xml data or data processor!
According to the BOL documenation of the ExecuteSQLTask you can populate a variable with a resultset. I specified a variable of type 'Object' and followed the instructions and everything is fine.
Now I have TWO issues.
1) I want to filter some unwanted rows from this variable and add into another recodset. For filtering i am using foreach loop and putting condition on variable. It works fine till here, but how to update another 'Object' type variable.
2) How to make the result set as source in a data flow task. I want the filtered result set to become source in a data flow
I am using SSIS to extract data from sql server and import into MDB file. In the process, under data flow task, I have used OLE DB Source Editor as source. Here i have choosen SQL Command as mode of data population. In the box below i have typed the following statements.
"Exec Site_Address"
I have used many temperory tables in this procedure. When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i click the button preview i can see the data. But when i click the Columns.... i dont see anything there. I am unable to see any columns there. This is getting to my nerves because, when i use OLE DB as Destination i am unable to map the columns and i get an error.
I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ??
Please help me to find a solution.
I will also paste the procedure code that i have used.
Create procedure Site_Address
as
begin
create table #Data_For_Site_Address_Table
(
unitid varchar(20),
city varchar(50),
cust_num varchar(40),
zip varchar(20),
CountryID varchar(20),
CreatedBy varchar(20)
)
-- tblcrdsiteaddress
insert into #Data_For_Site_Address_Table
select distinct * from
(select
(select top 1 fsu.ser_num
from fs_unit fsu
where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num
order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip,
CASE
WHEN ca.country like 'Luxembourg' THEN 'LU'
WHEN ca.country like 'Deutschland' THEN 'DE'
WHEN ca.country like 'Austria' THEN 'AT'
WHEN ca.country like 'Czech Republic' THEN 'CZ'
WHEN ca.country like 'Denmark' THEN 'DK'
WHEN ca.country like 'CHINA' THEN 'CN'
WHEN ca.country like 'CROATIA' THEN 'HR'
WHEN ca.country like 'Egypt' THEN 'EG'
WHEN ca.country like 'Germany' THEN 'DE'
WHEN ca.country like 'Hungary' THEN 'HU'
WHEN ca.country like 'Jordan' THEN 'JO'
WHEN ca.country like 'Korea, Republic Of' THEN 'KR'
WHEN ca.country like 'Poland' THEN 'PL'
WHEN ca.country like 'Switzerland' THEN 'CH'
WHEN ca.country like 'United Kingdom' THEN 'GB'
ELSE '- N/A -' END AS CountryID, CA.CreatedBy
from custaddr ca
) al
where unitid is not null
Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region,
TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action]
i am getting the following error in XML Source after generating the schema . if i click the columns
Error at Data Flow Task[XML Source[1]]; There was an error setting up the mapping . The root element of a W3XML Schema should be <schema> and its namespace should be 'http://www.w3.org/2001/XMLSchema'
I have 3 reports in my project which I've deployed.
When users run 2 of the reports they get the following message (the third report runs fine)
An error has occurred during report processing. Cannot create a connection to data source 'Windowmaker'. For more information about this error navigate to the report server on the local server machine, or enable remote errors
However the data source the message shows isn't the data source it's the project name.
I've tried setting the users to have various permissions under reporting services with no luck.
Thought it might be a windows server permissions issue, but they can run the third report. (I'm in the WS administrator group and the reports run for me).
