XML Source With Schema Filename In Variable.

Aug 13, 2007

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

Assign Variable For FileName Of Source File

Aug 10, 2006

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?

View 4 Replies View Related

How To Pass Filename As A Dynamic Variable To Flat File Source In SSIS ?

Sep 3, 2007

Hi,

I am migrating one of my DTS package to SSIS.

My task is to read the filename from a database table and transfer the flat file data in to a table.
In SSIS,I am able to fetch the file name using a Data Reader Source; but how to pass this fileName parameter to Flat File Source ?

In DTS I have used ActiveX script to pass filename variable as flatfilecon.Source.
Any help ?

Thanks,
Ravi

View 4 Replies View Related

Changing Source Filename In DTS

Dec 15, 2005

Hi!

Is there a way I can make the source file in my DTS dynamic so that every time I run it using ASP I can tell it which file to use?

View 1 Replies View Related

Getting FILENAME Into @variable In SQL

Oct 10, 2001

Does anyone know how to execute a directory listing of a file and return the file name to a variable in SQL? For instance, there's a file called c:datach20011010.txt and i execute the following:
xp_cmdshell 'dir c:datach*.txt'

or any other transact sql or procedure, how can i get the filename into a variable...all in sql?

FYI - I need to pass a filename to a process so that it can bcp the data into a temp table. But I don't know the filename at the time the process is setup/scheduled.

TIA,
Mike Nolen

View 3 Replies View Related

How To Read A Filename Into A Variable

Jan 14, 2008

I'm trying to read the filename from a file that I'm downloading by FTP (FTP Component) into a string variable so I can later use it for another part of the process, how can I do this?

View 2 Replies View Related

Problem With FILENAME As @variable

Jul 20, 2005

Hi.I am trying to create a database based on variables from xp_regread.Somehow I can�t pass @dBDir as FILENAME.I get the following error-message :Server: Msg 170, Level 15, State 1, Line 24Line 24: Incorrect syntax near '@dBDir'.What am I doing wrong ?I think the problem is somehow related to a datatype-problem orit is simply not possible to pass a variable to a "createdatabase"-statement.I have checked the xp_regread-output...naturally :) ...and it is correct.Hope someone can help :)regardsMichael----------------------------------------Source-code is as follows:USE masterGODECLARE @dBDir nvarchar(128)DECLARE @dBlog nvarchar(128)EXECUTE xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARESchool-ProjectIRPFDatabase','dBDir',@param = @dBDir OUTPUTSET @dBDir =@dBDir+'IRPF.mdf'SET @dBlog =@dBDir+'IRPF.ldf'CREATE DATABASE IRPFON PRIMARY( NAME = IRPF_db,FILENAME = @dBDir)LOG ON( NAME = 'IRPF_log',FILENAME = @dBlog)GO

View 2 Replies View Related

Filename Variable Trouble

Sep 15, 2006

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @jpg as int

Set @jpg = 0

While @jpg <4500

begin

set @jpg = (@jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @jpg, '@jpg.jpg',

bulkcolumn from Openrowset (Bulk 'D:DataPics@jpg.jpg', Single_Blob) as 'ImageFile'

View 6 Replies View Related

Output Filename Downloaded To A Variable

Sep 7, 2007

Hope I can make this clear...

I have to download a file via FTP from one of our vendors, the filename is MMDDYYYY.zip, this is a weekly file and is posted between Tuesday and Thursday, so basically I never know exactly what the file is going to be called, to fix that, I added a FTP Task that will download a file if the date = today (Today = Thursday, when the job kicks off), if this fails, try a file where the name = today-1 (Wednesday), if this fails, try today-2 (Tuesday), all of that inside of a Sequence Container and with MaximumErrorCount set to 3. This is fine and it works fine, good.

Now my other issue, the .txt file inside the zip file (1080.txt), after being decompressed, needs to be renamed to MMDDYYYY.txt, where MMDDYYY = to the file I downloaded, but I never know what the file is going to named (MMDDYYYY, MMDD-1YYYY, MMDD-2YYYY).

Is it possible to output the filename of the file that I successfully download to a variable, so I can re-use name?

Any other thoughts?

View 1 Replies View Related

Concatenating Filename And Variable/string

Mar 31, 2008

I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:

declare
@currentdate varchar(10)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'

Here is the SQL code I'm using:

exec xp_cmdshell 'copy "C:DevelopmentParticipant Limits ReportParticipant Limits Report template.xls" "C:DevelopmentParticipant Limits ReportParticipant Limits Report ' + @currentdate + '.xls"'
GO

The resulting file should have a filename something like "Participant Limits Report 3-31-2008". I get an "Incorrect syntax near '+'." error.

View 20 Replies View Related

Flatfile Destination Variable Filename

Jun 20, 2007

Why does the raw file have an option for a variable path and the flat file destination does not? Not having this feature makes it impossible to work with variable environments. Please add this option to the Flatfile Destination.

View 5 Replies View Related

Filename To Variable SSIS 2005

Mar 31, 2008

Hi All,

may be i'm asking a question that was discussed for billion time, but i can't find exact answer.

So i have access database that generates .xml or xls files by pressing button. So user select from drop down menu id ( e.g. 100,101,102,103 etc) and then file 101.xml or 102.xml or 101.xls or 102.xls is generating.

i need SSIS package to be configured to get filename is variable and then use insert data from xml or xls file to the related table

I don't understand how to pass file name through variable to SSIS package and then work with so called "variable".xls or
"variable".xml file. Could you please give detailed answers if it's possible?

Your early reply will be very much appreciated

Thank you

View 10 Replies View Related

SSIS: Flat File Source With Wildcarded Filename

Aug 14, 2007

My task is to write an SSIS package that picks up just one file from a directory and loads it into a database table. The filename is defined as being "ABC*.txt". So I must pick up only one file that matches that wildcard.

I can see two ways of doing this, but I can't get either to work:-
1. Use a Flat File Source connection and put the wildcard in the ConnectionString.
i.e. ConnectionString = "C:\mydirABC*.txt"
But SSIS doesn't seem to support that.
2. Use a Foreach Loop Container with a Foreach File Enumerator, and configure the enumerator as:-
Folder = c:mydir
Files = ABC*.txt
This works well, but loops round for as many files match the wildcard. Is there any way of forcing it to drop out after the first time round the loop?

Or am I missing a much easier solution?
Thanks.

View 10 Replies View Related

CREATE DATABASE Script Doesn't Accept A Variable For A FILENAME

May 2, 2007

I want to create a SP that creates a new database, so I script-ed out the db and paste the script into new SP gui (SQL 2000). I want to pass it a variable for the data/log file location that is not the default location. The original script looks like this:
CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = N'F:SQL SERVER FILESDatabasesdbName.mdf' , SIZE = 3118, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = N'F:SQL SERVER FILESDatabasesdbName_Log.LDF' , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS.
I replaced the path of the FILENAME variable like this:
CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = @DBPath, SIZE = 5000, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = @LogPath , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS,
declaring the variables as char(500). The error I get is "Incorrect sybtax near '@DBPath'.
Any ideas for workaround?
Thanks,
EJM

View 1 Replies View Related

Raw File Destination Access Mode Filename From Variable Problem

Dec 31, 2006

I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:Test.txt.

When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\Text.txt" ... so apparently XMLA is adding the escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.

What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.

View 3 Replies View Related

Integration Services :: How To Store Details From FileName Into Variable Using SSIS

Oct 19, 2015

I have one package through for each loop container I am loading all flat files into target table and its working fine.Now I my requirement is to capture the .txt file and loading into sqlserver table.Please check below file name.

CCCLOC_DDDLOC_LOC_20151203_240000_trigger.txt

from above file name I have to derived below detail.I have variable @File_Name which is used to stored the file name at rune time.Now I want to derived 5 values from the file name as below

Clientname=CCCLOC
Partnername=DDDLOC
Entity=LOC
Creationdate=20151203
Creationtime=240000

how to write expression for above situation.

View 10 Replies View Related

Using ForEach And Pass Filename To Flat File Source In Data Flow Task

Mar 11, 2008

How do i use the foreach loop container and pass each file found according to a specified pattern to a Flat File Source in a Data Flow Task Object so i can operate on each file found in the foreach loop object instead of having to specify a static file name

Thanks

View 4 Replies View Related

XML TASK XSL Variable: Exception 'Property New Source Has No Source Xml Text'

Dec 20, 2005

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 ?

View 3 Replies View Related

Xml Source Vs. Cascading Schema Changes

Aug 27, 2007

I'm using the XML Source to process a document with 17 elements in it. That leaves me with 17 outputs from the source.

Any time I make even the slightest change to the schema, it causes the metadata collection of the source to be updated. This causes everything in the data flow to have inconsistent metadata. This can take a very long time to fix, even though all I have to do is open the top transform and allow name mapping to work.

Is there any better way to make changes to an XML schema being used this way? Any tricks?

View 1 Replies View Related

Inline Schema - XML Source

Mar 15, 2007

I have a serialized XML that I got from a dataset. In my 'Data Flow Task', I bind the 'XML Source' source to this XML file. Since the XML file is having the schema along with it, I check the 'Use Inline schema' option. However, when I put a dataviewer to see the rows getting sent to the destination, I see that no rows are getting transfered. As you will see from the XML file I am trying to use, I do have one row to transfer.

I tried kepping the schema file and the content file separate and that worked. I am not sure if there are any inherent issues I need to take care of, when using inline schemas to transfer data. I have the SP2 for SQL 2005 installed.

Here is the XML file content:

<?xml version="1.0"?>
<DataSet>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="NewDataSet">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Table1">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="LastName" type="xs:string" />
              <xs:element minOccurs="0" name="FirstName" type="xs:string" />
              <xs:element minOccurs="0" name="Descr" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
</xs:element>
</xs:schema>
<NewDataSet>
<Table1>
<LastName>Agrawal</LastName>
<FirstName>Sumeet</FirstName>
<Descr>Consultant 1</Descr>
</Table1>
</NewDataSet>
</DataSet>

Thanks,

Sumeet

View 2 Replies View Related

Checking Schema Of Source Table

Sep 23, 2007

Greetings!!

I have a MsAccess db containing a table called Employees which i am transforming to Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.

However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :

Employees
empId int,
empName varchar(60),
empAge int

Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or are not null. If they are of type smallint,i have no problem.

However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it.

THe schema of Sqlserver table is same as of that of Employees in msaccess.

How do I do it.

Thanks ,
Lolsron

View 5 Replies View Related

Dynamic XSD Schema Location For XML Source

Jul 14, 2006

I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?

View 4 Replies View Related

Validating Source Schema Using SSIS

Sep 23, 2007

Greetings!!

I have a MsAccess db containing a table called Employees which i am transforming to a staging table in Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :

Code Snippet
Employees
empId int,
empName varchar(60),
empAge int

Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or string etc. If they are of type smallint,i have no problem.

However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it. THe schema of Sqlserver table is same as of that of Employees in msaccess.

I want to compare the schema of the incoming access tbl fields with my desired schema and all mdb's having data types that are higher or incompatible with the desired schema should be moved to the error.

How do I do it.

Thanks ,
Ron

View 1 Replies View Related

XML Schema In Variable?

May 13, 2008

Hi,

I'm trying to create the following Data flow task:
source
Type: XML source
Data access mode: XML data from variable
Variable name: user:Query_node (string)
Use Inline schema: true

Destination
Type: Recordset
Variablename:Queries_list (Object)

My problem is: how to generate an appropiate XML string into user:Query_node, that would also include an inline schema?

This is what i've done:

Use XML task to extract the desired node from xml file
xml file:

Code Snippet
<?xml version="1.0" encoding="utf-8"?>
<root>

<xs:schema id="Modules" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="root">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Module">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element maxOccurs="unbounded" name="Table">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Module_name" type="xs:string" />
<xs:element name="table_name" type="xs:string" />
<xs:element name="SQL" type="xs:string" />
</xs:sequence>
<xs:attribute name="Table_Id" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:choice>
<xs:attribute name="Module_Id" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

.
.
.
<Module_name>DOA</Module_name>
<table_name>DOA_Details</table_name>
<SQL>
SELECT
[Call#] AS [Order Number]
,[Part] AS [Part Number]
,Serial AS [Serial Number]
,[DOA Category] AS [DOA Category]
,[Install Date] AS [Installation Date]
,[Reporting Date] AS [Reporting date]
,[Shipment date] AS [Shipping date]
,[Customer Number] AS [Customer Number]
,[End User Number] AS [End user number]
,NULL AS [Manufacturing Problem]
,NULL AS [Logistic Problem]
,[Problem Desc] AS [Problem description]
,[Investigation] AS [Responsibility]
,Action AS [Action taken]
,NULL AS [Repeat occurrences]
,[Reason code] AS [Reason code]
FROM [Details$]
WHERE [Call#] not like 'LLLLLLLLLLLLLLLLLL'
</SQL>
</Table>
</Module>
</root>

XML task settings:
Opearationtype: XPATH
Sourcetype:File connection
Save opearation result into variable (overwrite)
SecondOperandtype: Direct input
SecondOperand: //Module[@Module_Id="DOA"]
PutResultsInOneNode:false
XPathOpearation: Node List

Since the Schema is not included in the resultset of the XML task, i'm getting a mapping error on the xml source.

Any help would be greatly appericiated!

Dror

View 1 Replies View Related

How To Concatenate Variables For RAW Output Filename I.e.@path + @filename?

Sep 7, 2007

Doesn't appear you can do this.

Am I wrong?

Please tell me I am.

View 11 Replies View Related

XML Source: String Types Lose Length Attributes In XML Schema?

Jan 25, 2007

I'm having a problem with the XML Source data flow component not transferring the length attributes from an XML Schema to the column attributes of the output table.

An example schema that I have is:

<?xml version='1.0' encoding='UTF-8'?><data xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'><xsd:schema> <xsd:simpleType name='NameType'> <xsd:restriction base='xsd:string'> <xsd:minLength value='0'/> <xsd:maxLength value='50'/> </xsd:restriction> </xsd:simpleType> <xsd:element name='Name' type='NameType' nillable='true'/> <xsd:simpleType name='FamilyType'> <xsd:restriction base='xsd:string'> <xsd:minLength value='0'/> <xsd:maxLength value='50'/> </xsd:restriction> </xsd:simpleType> <xsd:element name='Family' type='FamilyType' nillable='true'/> <xsd:element name='row'> <xsd:complexType> <xsd:sequence> <xsd:element ref='Name'/> <xsd:element ref='Family'/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name='data'> <xsd:complexType> <xsd:sequence> <xsd:element ref='row' maxOccurs='unbounded'/> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema>  <row><Name>Fred</Name><Family>Jones</Family></row></data>
When I reference file in the XML Source data control, it correctly infers that there are two columns, but the length of the strings in the columns are set as 255.

This behaviour appears to be at odds with the SSIS documentation (SQL Server Integration Services/Integration Services Object and Concepts/Data Flow Elements/Integration Services Sources/XML Source), which states (highlighting mine):

When the data is extracted from the XML data file, it is converted to an Integration Services data type. The XSD or inline schema may specify the data type for elements, but if it does not, the XML Source Editor dialog box assigns the Unicode string data type (DT_WSTR) to the column in the output that contains the element, and sets the column length to 255 characters. If the schema specifies the maximum length of an element, the length of output column is set to this value. If the maximum length is greater than the length supported by the Integration Services data type to which the element is converted, then the data is truncated to the maximum length of the data type. For example, if a string has a length of 5000, it is truncated to 4000 characters because the maximum length of the DT_WSTR data type is 4000 characters; likewise, byte data is truncated to 8000 characters, the maximum length of the DT_BYTES data type. If the schema specifies no maximum length, the default length of columns with either data type is set to 255. Data truncation in the XML source is handled the same way as truncation in other data flow components. For more information, see Handling Errors in Data.

Has anyone had any luck in getting string lengths automatically extracted from an XML document? If so, where I am going wrong?

Regards,

Michael

View 10 Replies View Related

Urgent Help Required: Passing Schema As A Variable

Nov 15, 2007

I am extracting data from multiple schemas with same structure. I have a table which have all the schema listed.Using Execute Sql task and For each loop, I am reading that table and string the value in a variable.
It is like this

"Select TK_ID,TK_TOKEN from " + @[User::Column1] + ".Tokens"

Column 1 is schema name.

Now I create a DATA FLOW TASK which has a OLEDB Source and OLEDB destination.
Using the option " Sql Command from variable" when I select the variable.
Since the Column1 will be evaluated at runtime , it doesnt give me any output columns to map to OLEDB destiantion and this throwing error.
OLEDB source output contains no output columns, The layout Validation failed.

Please help asap, my project is stuck.

View 9 Replies View Related

Integration Services :: Get Table And Schema Name Of Source And Destination In SSIS Package

Oct 6, 2015

How can I get table name and schema names of the source and destination in ssis package to insert into audit table....??

View 3 Replies View Related

SSIS Execution Error: FlatFile Connection Problem - FileName &&<fileName&&> Specified In The Connection Was Not Valid.

Apr 19, 2008

Hi Champs,

Scenario Configuration : VB.net 2005 Code, WebService for Executing SSIS on Server, SSIS deployed on the Database Server

Problem Description : We are developing windows applicaiton in which we call webservice which was deployed on the same server where SSIS packages are deployed.
Now from Code we are passing FilePath name in variable and execute the Package. But the SSis result says that
The file name "\computernamefol1fol2fol3fol4abc.txt" specified in the connection was not valid.

More Information:

1. Full Permission are given on this network folder.
2. Package executes successfully from SSIS development solution (BIS solution)
3. Deployed packed executes successfully from the Database Server.
4. From Development pc packege executes successfully.
5. Other packages deployed on the same server executed suucessfully with same configuration and scenario.

Only this package is not executing.

-- the only differece with this package with other is -

using ".txt" extension in Flat file connection and using VB Script task

Can any one suggest the appropirate solution for this problem...

Thanks
Tarang Pandya

View 21 Replies View Related

How To Add A Variable In 'Source' View ?

Mar 17, 2007

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 Related

Trouble Using XML Source From A Variable

Jun 14, 2006

I 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?

View 9 Replies View Related

Run Variable In DataReader Source?

Apr 7, 2006

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

View 14 Replies View Related

Table Variable As A Source

Sep 26, 2007

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

View 18 Replies View Related