SSIS Flakey Dealing With Share Names
Apr 19, 2007
I have found SSIS is showing some strange behaviour when dealing with share names for file connections.
I have been using Package configurations and file connections to pick up excel files are using the name of the Share as the path to the file location.
What is strange is that SSIS appears sometimes to successfully see the file and other times fails with the error "....does not exist"
eg I have run a package successfully picking up these excel files using the share name. I have then run the same package IMMEDIATELY again using the exact same files from the same share location and it will then fail with the above error.
Has anyone else seen this behaviour ?
View 5 Replies
ADVERTISEMENT
Jul 9, 2007
I want to skip running the SSIS data flow task when the source file is missing. We have a scheduler that copies the source file to the staging area. This SSIS package runs as SQL server job. So when a SSIS package fails due to missing file the remaining steps in the SQL scheduler won't execute. I want to handle the missing source file condition grace fully. Please advise.
Thanks in advance.
View 1 Replies
View Related
Jan 19, 2007
I'm exporting reports daily to a file share and I need to rename the reports with a pseudo time stamp.
Example: I have a report named "Disk Usage" and when I export (using a data-driven subscription) I want to rename it "Disk Usage - (Jan07)" - or something to that effect.
Can anybody tell me how to accomplish this.
View 1 Replies
View Related
Feb 12, 2008
Hey All,
This is my first time using the forums here nor am I a SQL programmer. I'm trying to help a coworker figure out how to access files from a network share (using a UNC path) from a different domain. Right now we have three domains - Production, Development, and the Local. There is a one-way trust setup with the development and local domains, so using cross domain accounts is easy. Unfortunately, we cannot setup one right now with our production domain to any of the other two domains. Is there a way to pass production credentials from our local or development domain servers to a production server share? Am I even approaching this in the right way? Maybe there is a different method, any help would be GREATLY appreciated!
-Andrew
Network Administrator
View 1 Replies
View Related
Aug 13, 2007
Somehow I have an impression this can be done.
Somehow I tried myself by modifying MsDtsSrvr.ini.xml to point it to network UNC share, but it didn't work for me. It gave me an error
"Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Additional Information: The storage localtion for the folder 'File System' cannot be accessed. (MsDtsSrvr)"
I checked my UNC permission, and I even allow full access from "everyone", but it still does not work.
I paste my MsDtsSrvr.ini.xml here too, and any help is greatly appreciated!
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>\test01mytest</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
View 1 Replies
View Related
May 4, 2008
I'm new to SSIS. I'm looking for basic pointers to return temp table created by a proc to be written to file shares. Any pointers/examples appreciated.
Thanks,
Siva
View 5 Replies
View Related
Sep 13, 2007
Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.
I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below
---------------------------------------------------------------
Message
Executed as user: FORTIESABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\Accfp1_data2_serverDATA2Arts&recAppsContactsCONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.
---------------------------------------------------------------
Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?
Can anyone help?
View 5 Replies
View Related
Mar 6, 2008
I am developing a package to restore a database from backup file on a remote server. I am having problems accessing the remote backup file when it is addressed via the admin share, in this case N$. It runs okay if a specific share is created but for some unknown reason fails via the adminshare.
I am executing the package job with a proxy account that is a member of the local administrators group on the remote server.
It appears that access via a remote admin share isn't possible from within a SSIS package. Is this the case?
View 2 Replies
View Related
Apr 14, 2008
hello,
I have two persons name in one column with the same ID.
How to seperate them.
like COLUMN NAME
John Smith, Sarah Baker
please let me knw
I want to put it another row
what to use in SSIS
View 1 Replies
View Related
Apr 24, 2007
Hi,
A collegue of mine is having some issues. I hope someone can help
Hi all,
This is my first call for technical assistance so go easy on me.
I'm having a problem in one of my SSIS packages. In brief, the process involves copying the rows from an untyped table to a typed table. There are about 45,000 rows in this table and during the copy ("OLE DB source" to "OLE DB destination") the process appears to hang for about 5 minutes eventually crashing with a "The buffer manager could not get a temporary file name. The call to GetTempFileName failed."
After several attempts using all the trace info I could master this is the order of events with some interesting numbers and facts:
1. The SSIS package goes swifty through "Validation", "Prepare for Execution" and "Execution" in less than 1 second, with "Execute phase is beginning" being the last message on the "Progress tab".
2. Using Performance counters I note that in the next 5 mins the values for "BLOB bytes read" slowly rises and then after a couple of mins so does ""BLOB files in use" the latter reaching a figure of 65534
3. When this figure is reached, SSIS starts creating thousands of zero-size files with the name DTS####.tmp (where #### is hex e.g. DTSB4C1.TMP) in the TEMP folder (C:Documents and Settings<username>Local SettingsTemp in my case).
4. When I started running this package there were 130 files in my TEMP folder; As soon as the combined total of files in TEMP reaches 65664 (i.e. 65534+130), SSIS starts producing the errors list which includes the one I listed above and eventually it clears the TEMP folder down to the original 130 files.
5. My conclusion (thus far) is that SSIS creates all these 1000s of tmp files but in my case hits some kind of maximum (either a folder limit or runs out of hex combinations for the file names) and then crashes.
6. The only thread I found on the internet suggested setting up an environment variable "BLOBTempStoragePath" and assigning a value of "C:Temp1;C:Temp2;C:Temp3;C:Temp4" so that SSIS can span across a number of "temporary" folders instead of the 1 default folder contained in the "TEMP" environment variable.
7. Setting the above environment variable in Windows 2000 did not work for me (tried it as both a user variable and a system environment variable). So here are the facts so far - ANY assistance will be hugely appreciated - I have no idea why all these temporary files are being generated - I have created SSIS packages handling data sets 10 times bigger than this one without these problems so I don't think it's size related.
View 3 Replies
View Related
Dec 4, 2007
I am a SSIS novice, and need help to do the following:
Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.
I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
In short can any one tell me a correct way of using a table name as variable in
Data Access Mode : Table name or view name variable ?
One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query
exec( 'select * from ' + @table)
or just
select * from @table
Any suggestions will be appreciated.
View 10 Replies
View Related
Mar 1, 2015
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
View 1 Replies
View Related
Aug 11, 2015
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
View 4 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Jan 10, 2008
Hello!
Is there an easy way to deal with this situation below when reading in data from a SQL Database:
int? myNullableColumn;myNullableColumn = Convert.ToInt32(datarow["datacolumn"]);
Where, ideally, 'myNullableColumn' would be 'null' if the value was 'DBNull.Value'. This does not work because Convert.ToInt32 will not convert 'DBNull.Value to null', but instead throws an error.
Is there a built in funtion that does do this?
Thanks!
View 8 Replies
View Related
Mar 14, 2008
Ok, I have a table with about 47000 records in it. I have the following query for that table:Select ReportType =
Case
When ReportType = 1 Then 'Uniquery Report'
When ReportType = 2 Then 'SABRE Report'
When ReportType = 3 Then 'Menu Report'
Else Null
End,
ReportNameTo_,
Frequency.Frequency as Frequency,
ReportDate,
ReportDescription
From Report
Inner Join Frequency on ( Report.ReportFrequency = Frequency.FID )
Where ( Active = 1 )
And ReportDate = ( Select Max ( ReportDate ) From Report Where ( Active = 1 ) )
And ReportID = ( Select Max ( ReportID ) From Report Where ( Active = 1 ) )
The idea is that i need to get only the last report based off of unique reportname. I added a computer column to the table to give me the ReportNameTo_, since my deliminator is the _. Now my issue is that I have 1 records showing (the last record added to the table), which is right for the query that is written, but wrong for what I want. I need to only return the last record for each unique ReportNameTo_. So as an example, my table has the following ID, ReportNameTo_, Date fields the data looks something like this:
1, 123_, 1/1/20082, 123_, 1/1/20083, 124_, 1/1/20084, 124_, 1/1/20085, 125_, 1/1/20086, 125_, 1/1/20087, 126_, 1/1/20088, 126_, 1/1/2008
I only want to return the following:
2, 123_, 1/1/20084, 124_, 1/1/20086, 125_, 1/1/20088, 126_, 1/1/2008
Hope someone out there can let me know how to do this... I am almost there, just not all the way.
View 2 Replies
View Related
Feb 15, 2002
Hello!
Just looking for advise on dealing with duplicates in database.
I have a contact table that have a bunch of duplicated customer records.
My goal is to combine all duplicated records into one record.
This involves couple tables:contact,contact history ,calendar.
All tables related by common column "accountno".
What would be the best approach for this?
Thank you,
Lena
View 2 Replies
View Related
Mar 15, 2002
I have the following query in a stored procedure. If there are no rows in the history file, it returns a null. If there some setting or function that would have it return a zero if no rows are found? I use the variable to do arithmetic later on and a null messes everything up.
select@MarketTotal = sum(isnull(MarketValue,0))
fromhistory
whereEmpUID = @EmpUID and
Shares > 0
Thanks,
Ken Nicholson
View 2 Replies
View Related
Jun 9, 2006
Hi i have the following :
select agent, name, surname, address, cust1_text01, cust1_text02, phone1,
case call_type_id
*when NULL then ''
else call_type_id
end as 'call_type_id'
from Record_T
* I have also tried when NULL then space(1)
yet the query still returns NULL when this field is empty ?
the idea is to always return data, even if the field is NULL to
replace it with an empty space or spaces.
View 3 Replies
View Related
Sep 28, 2007
Hello,
Question 1:
In my senario i've developed a system which utilizes 2 database, i've writen queries like db1.dbo.table1 join db2.dbo.table2 etc... Now that db2 is getting huge, client wants to shift it to another server.
I don't know how to modifiy my queries to cope with such situation. Could somebody plz tell me on how to you write queries involving two databases from different servers.
Question 2:
I'm maintaining second database (db2) to keep track of records of db1 which have been processed by my software, so that when db1 gets added with more records i can compare db2 table with db1 table to identify which records are new.
db1 is not my database and i don't have any control over that, (it's some erp db), is there any way of identifying which rows have been processed. Can the need for db2 be eliminated?
I'm using SQL Server 2005
Thank You,
Sandeep.
View 1 Replies
View Related
Jan 31, 2008
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?
For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.
Thanks!
View 4 Replies
View Related
Jul 31, 2006
hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks
View 1 Replies
View Related
Apr 4, 2007
I am trying to make a stored procedure in SQLServer Express.The question is related to this stored procedure / transact - sql. I think i am doing something wrong with datetime.Here is the stored procedure.The error i am getting is that:Msg 241, Level 16, State 1, Line 20Syntax error converting datetime from character string. ===================================== DECLARE @websiteID intDECLARE @dateFrom datetimeDECLARE @dateTo datetimeDECLARE @sortbystring varchar (20)set @websiteID = 1set @dateFrom = Convert(datetime, '2007-02-07 12:01:00')set @dateTo = Convert(datetime, '2007-03-07 11:59:00')set @sortbystring = 'Campaign'IF ISNULL(@dateTo, '') = ''begin SET @dateTo = @dateFromendSET @dateTo = DATEADD(d, 1, @dateTo)DECLARE @str CHAR(400)LINE 20: SET @str = 'SELECT dateEntry, c.name as Campaign, e.firstname as FirstName FROM entry e, campaign c WHERE e.campaignID = c.id ' + 'AND c.websiteID = @websiteID' + 'AND (ISNULL(' + @dateFrom + ', '''') = '''' OR e.dateEntry BETWEEN '' + @dateFrom + '' AND '' + @dateTo + '') ' + 'AND e.IP NOT IN (SELECT IP FROM IP) ' + ' ORDER BY dateEntry DESC'print (@str)===============================================
View 9 Replies
View Related
Dec 16, 2004
I have a stored procedure that takes less than 1 second in sql query analyzer to return my results.
I run this same SP in ASP.NET using a calendar control and using perf monitor I notice that for me from my dev machine my cpu utilization is sometimes over 40%.Is there any tweaks I can do to help decrease CPU utilization.
View 2 Replies
View Related
Jan 23, 2004
I'm looking for opinions here:
I have a stored procedure that has one required variable, and two optional variables, like this:
CREATE PROCEDURE sp_tariff_rule
@tariff_id INT,
@start_date DATETIME = NULL,
@end_date DATETIME = NULL
...etc...
I want the procedure to process
1) all data is no dates are presented
2) all data after the start date, if no end date is supplied
3) all data before the end date if no start date is supplied
4) all data between the start and end dates if both are supplied
Now, instead of an elaborate conditional, I added this to the WHERE clause of my SQL statement:
AND ((@start_date IS NULL OR service_date >= @start_date) AND (@end_date IS NULL OR service_date <= @end_date))
It works fine, but I want to know if anyone has a different/better way of doing it, or if there is a big bug waiting to happen here.
I typically don't like to create multipurpose routines in my code, but this is a better approach for my in a non-object-oriented world of SQL.
View 1 Replies
View Related
Nov 14, 2005
In SS 2000 it seems that there is no variable data type that can hold more than 8000 characters (varchar) or 4000 unicode characters (nvarchar). I've seen posts where multiple variables are spliced together to extend this limit. I am looking at performing string manipulations in an sproc and I need to be able to deal with the full 2GB/1GB limit of text and ntext field types. Is this possible? How do you deal with that?
View 14 Replies
View Related
May 31, 2007
Hello all. Got bit of a long winded question here...........so here we go lol.
OK.......ive got data on an Excel spreadsheet. Ive set the spreadsheet up as a linked server and i'm creating a set of insert statements from it by using the following code:
SELECT 'INSERT INTO TRAINREC (COURSE_NAME) VALUES ('''+
EMPLOY_REF + ''', '''+
FROM AtriumDD...['Employee Training Records$']
For most records this generates a correct insert statement.........for example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('153', 'NMA Panel');
However.........my problems start when the value for course name is containes an ' character. If it does the insert statement generated is incorrect. For example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('139', 'Annual Accounting in Lloyd's Market');
can anyone suggest any ideas on how to get round this? Also if i havent explained it clearly enough just let me know and i can try and expand on it.
Thanks for reading.
View 5 Replies
View Related
Jul 20, 2005
I have a stored procedure as a recordsource from a contacts table. Inthis example, users can enter parameters to limit contacts by firstletter of last name or company name or keywords:Example:@myName nvarchar(30) = null,@Alpha char(1) = nullSELECTContacts.ContactID, ContactType,CASE WHEN Contacts.ContactType = 0THEN Contacts.CompanyNameELSEisNull(Contacts.LastName,'?') + ', ' +isNull(Contacts.FirstName,'?')ENDAS CNAMEFROMContactsWHERE(Keywords Like '%' + @myKeyword + '%' OR @myKeyword is Null)So far, so good, but...The problem is I want to also give the user the option of filteringalphabetically by first letter. I can't figure out how to deal withnulls in this example (user doesn't enter anything as parameter@Alpha):AND(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1) ENDOR@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyName,1) END)Any help is appreciated,LQ
View 2 Replies
View Related
Apr 3, 2007
Hi,
I just realized something. In the old DTS package I am migrating, there is an ActiveX script that checks for a certain condition in a row. If the condition is true, then it does:
DTSTransformationStat_SkipRow
I just can't believe there isn't an equivalent functionality in SSIS.
However, so far, I have tried the following:
1.) Redirect file error output (on all columns in the file)
2.) Use a conditional transform to search for a text string in a column (the "bad" row has different text in it)
And still, I keep getting errors that there is an "impartial row" in the file. Yes, I know that - why doesn't the error redirection catch this? Why doesn't the conditional expression catch it either?
Am I missing something here? Is it just buggy? I find it hard to believe I have to work around something that worked just fine in DTS.
Thanks
View 26 Replies
View Related
Apr 20, 2007
What methods work for storing empty dates? I've read that some people pick an old date and use it to represent empty. I'm not fond of the idea, because then I'll have to strip that date whenever I display the field in my UI.
Any other ways to do this? I'm using SQL Server 2005 and C#.
Thanks!
View 4 Replies
View Related
Jul 17, 2007
hi,
I'm building a C# database application that access a remote sql 2005 database. For the moment I am using sql express edition. My application will be running in several REMOTE camps which only have an internet connection via sattelite. The sattelite connection has a very high latency. I am wondering what workarounds or solutions are available for this situation. All applications need to access the same database and preferebly be notified when changes take place on the database.
Thanks in advance.
View 1 Replies
View Related
Aug 31, 2007
Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
how to solve that problem?
View 5 Replies
View Related
Apr 2, 2007
Hi EveryoneVery new to .net and currently dipping my toes in the water with a small application, but getting to the point -
I have a form which has somel text fields that expect a date but which are not a required field so in other words the user can leave them blank.
The code behind page stores the information using a stored procedure which I add parameters to in the following fashion -
SqlParameter userdate = new SqlParameter();userdate .ParameterName = "@dtdate";userdate .SqlDbType = SqlDbType.DateTime;userdate .Direction = ParameterDirection.Input;userdate .Value = dtdate.Text.ToString();cmd.Parameters.Add(userdate);
Now if I leave the text field dtdate blank I receive an error because the above expects a date.
If I remove the line userdate .SqlDbType = SqlDbType.DateTime; I don't recieve an error but my stored procedure saves the date as 01/01/1900 or similar.
I believe this is because in my stored procedure the paramger dtdate is defined as @dtdate datetime
Obviously I want to have it so that if the user leaves the text field empty then no date is saved in the database and was wondering how other people tackle this scenario.
View 3 Replies
View Related
