Hi,
I'm using a script task as source and I need to read a global variable.
I tried stuff I found on the internet, but they all use Dts.VariableDispenser.LockForRead(varName)
This gives an errero on 'Dts.' saying it can not resolve it.
I am trying read certain attribute from a XML Source based on a particular attribute.
My XML source has the following Structure:<Database ID = "1" Source = "ABC" ></Database><Database ID = "2" Source = "ABC" ></Database><Database ID = "3" Source = "DEF" ></Database>.
I would like to fetch the list of IDs filtering upon Source = "ABC". (which should idealy give me 1,2)
I tried using the For Each Nodelist Enumerator By specifying the Enumeration type as Node and IDrect Input for the OuterXpathString as DATABASE[@Source="ABC"].
The for each loop fails giving the following error. Data is invalid. line 1 position 1.
Can anyone help me out with the same. Is there any other way around to obtain the required result?
Pranathi
Hi,
I have a commercial data source 3 party one for me to extract data from , i have been provided the exe and the DLL how i can use this in SSIS as a datasource please let me know.
Thanks,
The problem started off as:
I have a webservice written in VB.NET that simply returns a System.Data.Dataset. I want to use the dataset in SSIS.
So i decided to do this:
Save the returned dataset returned by the WS to an XML file. so it is sitting there, and looks like a fully valid XML file
<?xml version="1.0" encoding="utf-16"?>
<DataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="Name" type="xs:string" minOccurs="0" />
<xs:element name="SourceRecord" type="xs:string" minOccurs="0" />
<xs:element name="CombinedTitle" type="xs:string" minOccurs="0" />
......
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet>
<Table diffgr:id="Table1" msdata:rowOrder="0">
<Name>Member</Name>
<SourceRecord>99998</SourceRecord>
<CombinedTitle>Mr</CombinedTitle>
<CombinedFirst>Sample</CombinedFirst>
........
</Table>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
I then use the XML source connection to connect to it. It sees all of the columns correctly, but when i run and put a watch on it, or try to output the results to a .csv file, no records come through.
Any ideas on why there aren't any rows comming through?
i'm using SQL2005 with no SP1
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:
Operation type: xslt
SourceType: Variable
Source: user::RAS
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.
<?xml version="1.0" encoding="UTF-8"?>
<root> €¦
</root>
(When I store it in a file, I can see it in IE)
Have you got an idea ?
I am using an "XML Source Adapter" in an SSIS Package to try and load an XML file into a database table. But having trouble with a date field.
The XSD defines the field as:-
<xs:element minOccurs="1" name="DateTime" type="xs: string" />
The XML looks like this:-
<DateTime>12/31/2007 10:12:14.123</DateTime>
And the database table column is defined:-
[DateTime] [datetime] NOT NULL,
So in the Data Flow I have a "Data Conversion Transformation" converting [DT_WSTR] to [DT_DBTIMESTAMP].
However, when I run the package I get:-
Error: 0xC02020C5 at Load XXX File, Data Conversion 1 [13550]: Data conversion failed while converting column "DateTime" (15100) to column "DateTimeDT" (15422). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
So what how do I fix it?
I don't mind losing some miliseconds of precision, so I have tried configuring the error output of the Data Conversion for this field to "ignore failure" for "Truncation". But that seems to have no effect.
I'd be happy to change the XSD to:
<xs:element minOccurs="1" name="DateTime" type="xs:dateTime" />
But the "XML Data Flow Source" doesn't seem to support the dateTime data type.
Any suggestions appreciated. Thanks.
Hi,
I have an Excel spreasheet - the first column is text, the second numbers, the third a mix of the two. If I point An Excel Source at this in my data flow, it will import the first two columns without problem, but not the third: all cells containing text are being imported as nulls, but those containing numbers are imported just fine.
Even if the numbers are stored as text, they are converted into numbers at import and genuine text is still discarded. It's treated as if the entire column is numeric if there's just one numeric value in it.
I can get around this by creating a .csv or .txt file from the excel file, but that will add an extra layer of admin to this process and I'm tryuing to make it as seamless as possible
I have a simple SSIS package -> It reads a local text file which has 10 rows of data ( id, name, telephone # ) and puts it into a table.
It uses the "SSIS Flat File source" to read and a "SQL Command" to insert into the table. I can see that it reads line by line and puts each line into one row in my table.
Now, my production data is over 5 GiG of mainframe data and it seems their data is arranged in some hierarchical form.. so the position or arrangement of data in that file is important.
I pulled the data using my package and as far as I can see , my SSIS package pulled one line at a time ( from the flat file) and pushed it into my table. For each row, I also created an identity column in my table to be able to identify the positional arrangement of the hierarchical data and then use relational mappings to suit our business needs.
In all of this, my assumption is -
"SSIS reads one line at a time, inserts to my table and goes down to the next line .
It does NOT read a snapshot of rows from the flat file so as to write them into the table using internal ordering methods based on that particular snapshot "
My question is .. is my assumption correct ?
I keep receiving errors while using variables to pass values to different part of my package. For example ...
Error: 2006-05-04 10:31:59.84
Code: 0xC00470EA
Source: EdwPostProcess
Description: Reading the variable "User::GvPathRoot" failed with error code 0xC0010009.
End Error
The way my variables are constructed is :
First the global variables (Gv*) are set by the parent package;
Then local variables (Lv*) are set using the EvaluateAsExpression property and giving it an expression that takes the Gv* variable and concatenate a string to it.
At execution time, while the expressions are resolved, I get the above error while it was resolved correctly in a previous task.
I tried different method including duplicating my variables but without success. I'm running out of ideas
Gilles
I am trying to ETL from excel source to SQL table. When I created the package my excel had certain number of rows. Later if I add (append) few more rows in the spreadsheet ... the ssis package (also in excel source preview) not reading the newly added rows. Suprisingly it is reading new rows if they are inserted in between existing rows.
Any idea why ?
In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.
However this value is not then working in the following line:
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
This statement is not reading any value for ProjectID and so is selecting all from the table.....
How do I write this statement to pick up the variable above?????
Thanks in advance for your help!
Clare
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As New Data.DataSet
Dim da As Data.SqlClient.SqlDataAdapter
Dim strSQL As String
Dim ProjectID As Int32
ProjectID = Request.QueryString("ProjectID")
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
Dim connString As String = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"
da = New Data.SqlClient.SqlDataAdapter(strSQL, connString)
da.Fill(ds)
ds.Tables(0).Columns.Add("imgFile")
For Each tempRow As Data.DataRow In ds.Tables(0).Rows
tempRow.Item("imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))
Next
ImgGrid3.DataSource = ds
ImgGrid3.DataBind()
End Sub
It looks like its not possible to both read and write the same variable from a script using the conventional Me.Variables.<variable> syntax.
I can only assign a variable as Readonly or ReadWrite and not both. If I assign it ReadOnly I can only access it in the PreExecute subroutine. If I assign it ReadWrite I can only access it in the PostExecute subroutine (in fact doesn't this just make it WriteOnly in fact?). So I can only either read in or read out a variable using this syntax, noth both. Is this right?
So the read and write a variable to a script, the VariableDispenser approach is the only option to use. Is this right? and is it documented somewhere that this is how to use variables in scripts. Thanks.
I have a tab delimited flat file with say 60 columns. All columns can have null values. The file contains a blank tab for nulls.
View 25 Replies View RelatedHello,
I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J.
THE CODE I USE TO CALL THE SPROC
Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty)
THE STORED PROCEDURECREATE Proc [prs_VerifyUser
@regid uniqueidentifier,@regemail nvarchar(250) OUTPUT
ASBEGIN
IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid)
BEGIN
SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid
Return 1
END
Return 2
END
Hi, sorry again, l've a package which call the FTP tasks. In design-time, it works fine. While l put it into SQLAgent to run in schedule. It fails. The package log file said,
OnError,MAXIS-SYSOPT,NT AUTHORITYSYSTEM,FTP Raw File Motorola,{b3b941f5-ebc2-4b38-b314-87ff4827c2a8},{E93D51C5-539E-4CEE-B616-BBCFF9FA813E},4/18/2006 3:18:23 PM,4/18/2006 3:18:23 PM,-1073573489,0x,Unable to connect to FTP server using "Metrica FTP".
Does anyone know what's happening?
does any one how to search for a word in a text file and return it back using the script task in ssis?
the file may contain data like this
POSITION SMSMSS20051230000
S ,,751600 ,,20051110,,20051230,20051230
S ,,751600 ,,20051110,,20051230,20051230
S ,,751600 ,,20051110,,20051230,20051230
S ,,751600 ,,20051110,,20051230,20051230
S ,,751600 ,,20051110,,20051230,20051230
what i am looking for is to be able to parse and get the date which is present in the first line "POSITION SMSMSS20051230000" as "20051230"
and then return that as a variable ..
Thanks for any help in advance
smathew
There are 3 views in VS IDE, Designer, Code and Source. In source view, there are html codes, how do I add a variable into it ? For example: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:JJConnectionString %>" SelectCommand="SELECT * FROM [Tbl]" ... How to change the SelectCommand to like:SelectCommand="SELECT * FROM [Tbl] WHERE [CREATEDBY] = '" & User.Identity.Name & "'" The User.Identity.Name is not valid in source view, but i need it to work, are there any way ?
View 1 Replies View RelatedI have a SP that outputs a valid XML record set. Using a Execute SQL
Task, I can put the XML results into a string variable. When I try to
use the variable in an XML Source nothing happens and I get no error..
If I access the same XML from a file, I get the desired results.
Any ideas why?
Hi all,
I am trying to have a DataReader Source that can run
a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand
that connects to an ODBC connection. If it is possible by any way,
please let me know. Thanks in advance.
Daren
Hi,
I am using a table variable as a source in my OLE DB Source task ( I declare and populate this table variable from TSQL script) to load data in my destination table however the data gets lost after the task, ie I can preview the data from the OLEDB Source task but when I go to the next task which is multicast tranformation the data is lost, I have used data viewers to see the data but they dont have any rows, any help is appreciated.
Thanks
I read several posts on this topic and I would like to confirm my understanding.
This question has to do with parameterizing the select statement in the OLE DB Source editor. Initially, I thought I could use Data access mode: SQL command, and somehow use a user variable in there to build my Select statement.
But in researching further, it looks like I need to store the entire SQL command in a one long string variable (let's call it A), where A is built off of another variable that has my parameter. I then use A in Data access mode: SQL command from variable.
Am I correct? And is there not a way to accomplish the samething with only one variable that is your parameter value?
It seems a bit clumsy to store the entire SQL expression in a string variable.
I have a package-level variable [User::viewName], type = string, containing a view name. I want to setup an OLE DB source to use this variable value as the source, so Data access mode = "Table name or view name variable". The Variable name dropdown contains [User:viewName], so I select it. When I click OK to leave the edit dialog I get the error:
The variable User::viewName is required to be of type "VT_BSTR".
Only variables of type String occur in the Variable name dropdown; if I try changing it to a type other than string it doesn't occur in the dropdown. What is VT_BSTR and how can I change the variable type to it?
Using Jamie's, John's and Rafae post i have got this far.
My scenario demands me to do an incremental load. So i have followed your article and have created a one variable 'vDate' (Dataflow Task, Datetime, 3/3/2008) and another variable SourceSQL (Dataflow Task, String). I have set this SourceSQL evaluation to True and have added this;
"select * from stagingperfdata where startdatetime = "+ (DT_WSTR,10) @[vDate].
I want to call this within a OLE DB source, so i select the data access method to SQL command from variable. My variable name is visible and i am able to select it and then save and debug. I also have a derived column transformation to increment the variable vDate by one day.
The flow is successful but there is no records written to the target.
I tried the same query (available in the variable's value column) in SSMS and it is retrieving 12 records.
I went back to the Ole Db source and wanted to preview the data, but i get "Query timeout expired (Microsoft SQL Native Client)" error.
Am i missing something fundamental?
We can pass XML to the XML Source in a variable, but I haven't seen anywhere how much data can be passed this way? Is there a limit beyond the limits of system memory?
Also, what data types are valid for the variable? Just String?
We are storing incoming flatfiles into a text field in a table and then we are processing this table on a regular basis. What I would like to do is to get this flatfile from the textfield and populate a flatfile source with it, but so far I have only been able to do that with XML files as there are no option for doing that with the flatfile source. Using the disk as a temporary storage for the flatfile is prohibited.
Does anyone have any suggestions on how to solve this?
Hello. I€™m new to SSIS. This forum and Kirk Haselden€™s book are my teachers. I€™m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I€™m simply misunderstanding the best practices of SSIS.
I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.
How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can€™t write a query against them in the Data Flow, since there is no connection manager that allows me to €śconnect€? to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?
Any advice greatly appreciated. Thanks in advance.
Dear All,
I have created a DTS Package in Integration Services 2005.
Within the DTS Package declared a variable named xxx and passed a value 1234.
In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined
Property = [DataReader Source].[sqlCommand]
Expression = Variable name.
Now in the DataFlow Task Canvas dropped DataReaderSource.
How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.
regards
Sufian
Is there a way to configure the XML source component to use a schema file whose filename is in a variable? Now I know it doesn't make any sense to have an XML source whose schema can vary, however, I have a parent package with a ton of child packages, all of which process the same XML input using the same schema. What I would like to do is for the parent to load the filename of the XML input file into a variable, and the filename of the XML schema file into another. Each child would have its XML source configured to pickup these variables from the parent, so that if the location or name of the schema changes, I don't have to edit each package one-by-one. However, it doesn't seem possible to specify anything but a filename for the schema. Any suggestions?
View 5 Replies View RelatedHi!
These problem does't look like a problem, but i am not able to solve it...
I have a stored procedure (in an Execute SQL-Task) what returns a few rows. These rows a written in an object variable. No problem.
Now i want to use these result set in a dataflow as source. How can i manage these?
1. Use a script task what returns the whole result set from the object variable? How?
2. Is there any other possibility to use the results of a stored procedure in the data flow?
All ideas are urgently welcome...
Torsten
I am trying to make a SSIS package that will loop trough all files in a directory and load information from them.
I can do this with Raw File Sources since they allow me to use a variable name as the file path, but I cant seem to do the same with Flat File Sources. Is there a way to change the connection to a Flat File Source on each iteration of a loop? Actually, if this is possible with all types of file sources (like excel files) I would love to know about it too.
I was thinking about renaming the file through a script task but that does not seem like the most elegant solution so decided to see if some one here knows of a more proper way before I go that direction.
Hiya guys,
The question:
Can I design data flows with an XML Source pointing to file system xml files, and but run them against variables? I can see the properties at design time (Accessmode=0,1,2; XMLDataVar="User::XMLData), but the XMLSource object doesn't appear to have expressions enabled to change this at runtime, nor do these properties seem to be exposed to configurations.
The scenario:
I have a package that reads through (potentially thousands) of XML files, and having identified the embedded message "type", transforms them via XSLT to an easier XML format (XML Datasource can't cope well with multiple namespaces etc). Then send this the new file off to a type specific dataflow task to send contents to database.
For performance (and other) reasons I'd rather XSLT to a single shared variable and use this variable as the XML source in the data flows (Rather than writing out to the filesystem [via xslt] and immediately reading it back in each time [via datasource]).
But designing the data flow task using a variable as xml source is frustrating for a bunch of reasons - not the least being:
(a) The variable needs to be populated with sample xml data at design time. But this could only ever match one dataflow at a time (fine at run time, but painful at designtime - leading to validation errors popping up all around the place.)
Yes I could have a seperate variable for each xmlsource - but that just makes things more complicated up front and also leaves me with 20+ large, type specific, xml variables floating around at runtime of which only one is ever being used per import - which just doesn't feel right to me.
(b) populating the variable with sample xml at design time is painful due to it being a string - and not accepting hard returns etc that are in text files, design time changes etc)
Using a file XML source at design time is infinitely easier, when I get a design time change - I can just amend the XSLT and run the appropriate task to produce a sample XML file to design against.
Any ideas?
Ta.
Gus.
We have a job running the SSIS Package. I am working with that SSIS Package. The SSIS package has a script task. In the script task they used the variable of source path. I want to change the value of that variable (Need to modify the source path).
I modified the variable value in that package and in the config file but getting error message
Exception has been thrown by the target of an invocation.
I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.
I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.
Any ideas or suggestions?