I have a text file that is being insert into a table in a remote db. I have a dev server named dbname_trunk and a production server named dbname. The dataflow task refers to
Actually, I know that doesn't work. What I need to know is what would work to accomplish my purpose. Ultimately, I would like to put the value in the configuration file.
Hello,to accelerate loading xml data from many files into a table using openrowset (bulk...) I want to use a variable in the file specification and increment it within a loop similar to this:
declare @datnam varchar(100); DECLARE @MyCounter int; SET @MyCounter = 1; set @datnam = 'c:XML_DatenPOS_LOG_200608_'+ltrim(str(@MyCounter))+'.xml'; INSERT INTO GK_TO_KFH_ADAPTER_XML_NS (LOC_ID, MSG_CONTENT) SELECT @MyCounter, MSG_CONTENT FROM ( SELECT * FROM OPENROWSET (BULK @datnam, SINGLE_CLOB) AS MSG_CONTENT) AS R(MSG_CONTENT)
But I got the following error:
Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '@datnam'.
Is there a way to this in that manner? Or is the bcp utility an alternative?
I had problem when combining OpenRowSet and SP_EXECUTESQL, when i tried to run the following query, it complaints that RESID is not declared. any idea how should i put the query so i will pass @RESID as 1 of the parameter? BTW, i know that the SP_EXECUTESQL is able to run query which length up to 8000, but how about the parameter?
how do you store a datareader propety to a variable? Below is my current code. I have already declared my connectionString and sqlComm objects, as well as the userName and such. I need to store the value from the dr to the variables, UserName, UserPass, and serverName. ThanksTry sqlCon.Open() dr = sqlComm.ExecuteReader While dr.Read userName = dr("uName").ToString LogInfo("userName = " & userName)
First and foremost, I'm officially and thoroughly confused between Variable, Expression and settings. I need help clearify this up for me.
I'm going to use the Flat File Connection as an example.
I have:
> A global variable named: FileToImportFullPath (string) that stores the full path of the file I want to import. Note: the file path will change has the package executes (the location of the file depends on a number of conditions, namely the Date and Time of the package execution)
> A Flat File Connection Manager used by a Flat File source in the package's main Data Flow
So ideally, I want the Flat File Connection Manager's ConnectionString to be set to whatever the value of FileToImportFullPath variable is at the time. To accomplish this, I set the ConnectionString Expression to equal to FileToImportFullPath. First question, if I set the ConnectionString Expression, is it okay for me to leave the ConnectionString property (i.e. in the Editor or the Property Editor) blank? Second, whenever I leave the ConnectionString property blank, I will get a warning stating: "A valid file name must be selected". Since this is a warning, I ignored, but during exeuction, the value really is blank. Also, I'm 100% certain that the FileToImportFullPath variable is set correctly before the Data Flow step is executed.
All in all, I'm just confused if an object's property must be set if there is already an express for it (e.g., in the file move task, the source and the destination properties).
I am having a hard time setting the executable path for an Execute Process Task in SSIS. I have a variable that is initialized at package statup which holds the path to an executable in Windows. When I set the property "Executable" Path in an expression, I get a warning that the path for the executable is not set. One workaround was to try and initialize the variable with a bogus path with the hopes that the "correct" value will be written on run-time. NO LUCK. I still get the error and I cannot run the package until I put a static path.
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 am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing "OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere" " on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager. The Expression is as follows
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While "Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures. 2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ? 3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
How come when I run the script: select * from openrowset ('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=\inawwwpro01d$atrUploadACK102.xls' , 'select * from [sheet1$]') from Server A I get a RESULT, and when I run from Server B I get the following error: Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server. Both servers are using IDENTICAL SQL Logins. Both servers are SQL 2000 SP2.
When I run the script on Server B logged in as SA, then I get a Result!!!
how can i use openrowset. my aim is to develop a import and export data from diff. server . i am using vb.net 2003 , sql sever 2000. when i am running openroeset function with window or server authontication it is showing 'OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
anybuddy help me , pls. tell me the right solution.
Hi! I decided to use OPENROWSET for importing data from an excel file into a sql table.When I import the data,I have a problem:
-not all the data is imported to my sql table -some values in the sql table are different from the ones in the excel file For example,a value that in the excel file is:87878787 will be in the SQL table:8.78788e+007.
Hi Folks,I am trying to load data from a table in MS Access to SQL Server 2000using T-SQL OPENROWSET. When I select data from the remote database (MSAccess) using SQL Query analyzer, the columns do NOT appear in the sameorder as seen in Access directly.For e.g. if Access table has columns Cy, Cx, Cz the output in Queryanalyzer appears as Cx, Cy, Cz. It appears to arrange the fieldsalphabetically. This causes problems when I do a 'insert into select *from' as the field definitions do not agree.Is this a bug or is there a setting in Access/SQL which I am missing?Also, please let me know if there is a workaround for this issue.Thanks in advance!Bhaskar
I am trying to use OPENROWSET in SQL Server to connect to my Access database, but I keep getting the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file 'X:SetupDatabaseKDB_X2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I have checked the database, it is not in use, and there is no password set on the database that would prevent me from getting access to it. Any ideas? Here's the syntax I'm using:
SELECT * FROM OpenRowset('Microsoft.Jet.OLEDB.4.0', 'X:SetupDatabaseKDB_X2.mdb';'Admin';'', subPSEL_PList))
I having an excel file called TEST.XLS (in c: drive) which has 3 columns (c0,c1,c2) & 8 rows c0 c1 c2 1 a A 2 b B 3 c C 4 d D 5 e E 6 f F 7 g G 8 h H
In sql server i have one table called TEST which has three columns c0 char(10) c1 char(10) c2 char(10)
when i runnig the following query
select * into TEST from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:TEST.xls;HDR=YES', 'select * from [Sheet1$]')
i am getting error like this
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Version : Sql server 2000 & Excel 2002
Anybody having any idea why this error occuring? thanks in advance... Philkos
I am having trouble exporting the correct data from sql server to an excel spreadsheet using 'OPENROWSET'
The problem is that although the data from my sql table is say : 'a',1,2,3 , the excel spreadsheet sees the data as : 'a','1,'2,'3 ..1,2,3 are of course numbers NOT text, its just that the driver has put a single appostraphy before the number !
I know there is a bug with the ISAM driver but has anyone managed to solve this or has anyone have any alternatives ?
I'm doing an openrowset query on an excel sheet. (Using SQL Server 2005) Everything works great, except that I have one column that has both numeric and text data in the spreadsheet. The query returns that column as datatype varchar but puts nulls in the rows that have numeric data in the spreadsheet.
Any suggestions?
I run:
select * from openrowset('microsoft.jet.oledb.4.0', 'Excel 8.0;database=[filepathandname], 'select * from [Sheet1$A4:G5000]')
and would like to create views for each distinct table, using openrowset. An added complexity is that the library name depends on the company code (i.e. BISxxSET.ALPA0A turns to BIS03SET.ALPA0A for company 03 whereas BSFFBUA.ALPA1A remains intact).
Hy Forum !I tried to do somsthing like this:CREATE PROCEDURE dbo.maches ASselect * from openrowset('Microsoft.Jet.OLEDB.4.0','C: empFehler.mdb'; 'Administrator'; ' ' , Fehlerliste)This user 'Admistrator' exists and there is no password for the .mdbfile. In the end the syntax checker tells me:Fehler 7303: Datenquellenoblekt von OLE DB Provider'Microsoft.Jet.OLEDB.4.0' konnte nicht initialisiert werden.I'm using Access 2000 and SQL Server 7.Thank you
I have an excel file on a web server on a different location to the sql server. I want to use OPENROWSET to query the excel file, but it doesn't seem to like using a URL.
Is this the case, or am I doing it wrong? Works fine when I use a local address (eg c:excelfile.xls) when testing locally, but I need to use a URL in production.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=http://mydot.com/excelfile.xls','SELECT * FROM [page1$]')
I have an access database from which i want to use data in sql server if a table has column with values like 6.32404E-244 i've got an error message like:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)' returned invalid data for column '[Microsoft.Jet.OLEDB.4.0].Stoc_Max'
a simple select like 'SELECT * FROM OPENROWSET(.....,table) gives me this error.
We have lots of OPENROWSET usage here, all with embedded user id's and passwords. In an attempt to find a way to eliminate such a foolish practice I created a system DSN on my PC and tried to use it to connect to our sqlserver 2000 database as follows:
SELECT a.* FROM OPENROWSET('MSDASQL','DSN=TargetDB', 'SELECT * FROM table1') AS a
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. Authentication failed. [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I thought the reason to create an ODBC system data source was to not have to enter credentials in code but use the credentials in the system DSN. I guess I was wrong. Is there any way to not embed user id's and passwords in OPENROWSET code? Having user id's and passwords in code is dangerous and foolish and I am surprised people do it.
