InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 01:25:26 GMT20http://www.sqlservercentral.com/Forums/Topic815162-148-1.aspxHi,I have .txt that i am trying to load into sql table using SSIS2005 and the destination is SQL 2005.The text file will have a header and data.The format of the .txt will be as followsCodeValues for the period ending 11/04/09CodeValueAZX123XAZS143XADSE23VThis has to be loaded into a table with the date from the header as a columnFor example,table format should be like thisCodeValue EndDateAZX123X 11/04/09AZS143X 11/04/09ADSE23V 11/04/09I am able to trim the hearder but not able to add this date columnfrom the header to the rows,any ideas of to do this?Thanks,Fri, 06 Nov 2009 13:11:05 GMTUnionAllhttp://www.sqlservercentral.com/Forums/Topic813942-148-1.aspxI have a directory named e.g. dir1under which there could be one or more datecoded directories: [b]20091101_1220091102_03abcxyz[/b]I'd like to get the return of list of those date-coded subdirectories, i.e. 20091101_1220091102_03so that I can go under them and get files, perform other actions, etc...How do I do that? I tried Foreach Loop container using Foreach File Enumerator. However, that gets only the Files, not the directories for me.Thanks for any help in advance!Wed, 04 Nov 2009 18:03:46 GMTktladyhttp://www.sqlservercentral.com/Forums/Topic815047-148-1.aspxHow can I SSIS Concatenate DTS.Variables in Script Task? sDateTimeStamp = sMonth & sDay & sYear & sHour & sMinute & sSecond Dts.Variables("DateTimeStamp").Value = sDateTimeStamp sFilePath = "F:\SQLXXX-S01-BAK-F5a\MSSQLI\Backups\ETL\QQQ_Reports\ADR_BB_J9\Reports\" sFilePrefix = "ReportsPETS_XX_WW_DailyCount" sDateTimeStamp = sMonth & sDay & sYear & sHour & sMinute & sSecond sFileSuffix = ".csv" Dts.Variables("gsFileName").Value = sFilePath & sFilePrefix & sDateTimeStamp & sFileSuffix'Dts.Variables("gsFileName").Value = Dts.Variables("gsFilePath").Value & Dts.Variables("FilePrefix").Value & Dts.Variables("DateTimeStamp").Value & Dts.Variables("FileSuffix").ValueFri, 06 Nov 2009 10:00:09 GMTKent Krug-210111http://www.sqlservercentral.com/Forums/Topic814547-148-1.aspxCan you concatenate variables at the Package Level like in DTS with Global Variables?Thu, 05 Nov 2009 13:48:43 GMTKent Krug-210111http://www.sqlservercentral.com/Forums/Topic813512-148-1.aspxHi,I have a package which after executing sends the destination file to the user through Mail as an attachment.The package is executing properly without any error in SQL server development environment. The error is happening only when executing the Job in SQL Server Management Studio.I am getting the following error.[b]Code: 0xC001000E Source: Package Name Description: The connection "SMTPConn" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error Error: 2009-10-29 11:01:31.16[/b]Where SMTPConn is the name of the SMTP Configuration Manager containing details of the SMTP server from where the mail has to be send.Even though the error comes after executing the Job, the destination file is send across through Mail to the user as it should be. So package is serving the purpose what it should do but I need to remove the above error also.Any suggestion regarding the above strange issue.ThanksWed, 04 Nov 2009 03:41:55 GMTpower.ravi2000http://www.sqlservercentral.com/Forums/Topic813321-148-1.aspxI'm trying to debug (actually to see the result set) an Execute SQL Task but not having much luck. I set a breakpoint on the task, and right clicked/Execute Task, pulled up the Watch 1 window...but the name is grayed out and I cannot enter anything. There is no option to add. What am I doing wrong? Also how can I drag the variables to this window when they are no where to be found?Tue, 03 Nov 2009 15:59:40 GMTmbokkerhttp://www.sqlservercentral.com/Forums/Topic815473-148-1.aspxHello,I've to perform a merge inner join and i have two data sourcewhich gets records after performing joinsI've to sort both of the data source on 4 columnsSource A has: 1,34,000 recods and has 15 columnsSource B has: 1,74,000 recods and has 4 columnsHow ever data source gets all 1,34,000 records but it do not sort the records on my local maching, It kept running for 3 hrs...please suggest!!!Sat, 07 Nov 2009 13:38:49 GMTPavas Malviyahttp://www.sqlservercentral.com/Forums/Topic589411-148-1.aspxHi,I am novice to SSIS and in process of ETL tool selection.I am trying to connect to a Informix database.I am able to create a ODBC connection to that Informix database and test it successfuly.I added One Informix database as data Source in SSIS.When I tried to create a DataSource View on one table I got below error.ERROR [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (iclit09b.dll)Tried to google ..without much help.Also suppose i am able to connect to Informix ..I will have to use Data Reader Source Object to connect to it (as i need to use ODBC connection for IBM Informix).Is this a correct statement ?or I have to use OLEDB data source ?When I a look into Data Reader Source task I see this error (i can not modify its content)Error at DataFlow task Datareader Source (95)Runtime connection manager with ID can not be found verify that connection manager collection has a connection manager with that IDThanks and Regards!AbhishekTue, 21 Oct 2008 13:20:45 GMTabhadu2006http://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxHi all,I have a table, containing 5 columns: Column1, column2, column3, column4, Filename and a .csv file with name that every day is different and it contains a date when the file is received. The name of the file is like YYYYMMDD<4 characters that are random>003.csv (example: 200911053w3r003.csv). At the same time I may have 0, 1 ore more files. I have to import data from that file into a table.My first question is how to fill the FileName column in the table? Until now, I made a SSIS that loop through all .csv files in the folder and import data from them but without filling the file name column.Second how to handle this logic for sending emails:If the file is imported successfully then send email with message: The file is importedIf there is now file to import send email with message: There isn’t any file to importIf the format of the file name is not as I described send email with message: Wrong file format.If there is a problem with data in the file send email with message: The file can not be importedI have read a lot of articles about processing files using SSIS but I couldn’t fine how to deal with those two requirements.Please, provide me a solution with detail explanation if it is possible because I’m new in SSIS…Please, It is urgentThu, 05 Nov 2009 14:52:05 GMTDr.Dhttp://www.sqlservercentral.com/Forums/Topic814916-148-1.aspxI can create a Global (Package Variable) but how do I write to it using a Script Task or Expressions that have a Global (Package Scope).My problem is that I a Flat File Connection and I use an expression to create the Connection Name using Variables but I can't reference that Connection Name in the next task Send Mail when I attempt to send the file using an expression. I need to be able find out how to write to a Package Variable.I have no issues creating variables that are scope is local (limited) to the specific task.Fri, 06 Nov 2009 07:18:34 GMTKent Krug-210111http://www.sqlservercentral.com/Forums/Topic814752-148-1.aspxHi All,I have a string in the following format which I am trying to convert to SQL Server datetime.yyyymmdd24missEg. 20091102165659I have tried casting using the following statement:Select cast (20091102165659 as datetime)Error received - Arithmetic overflow error converting expression to data type datetime.I have also tried using a 'Data Conversion' transformation with Data Type; 'database timestamp [dt_DBTIMESTAMP]Error received - [Data conversion [638]] Error: Data conversion failed while converting column "timestamp" (163) to column "timestamp" (655). The conversion returned status value 2 and status text "The value could not be converted because of a potentail loss of data. The destination field in SQL Server is set to standard datetime.Any assistance would be really appreciated.Regards,ChrisFri, 06 Nov 2009 02:09:03 GMTchrismgarveyhttp://www.sqlservercentral.com/Forums/Topic815207-148-1.aspxI have a package that creates spreadsheets. The issue is this.The SQL table has the column defined as float.This column is created on the xls as string. I looked at the advanced editor in SSIS for that spreadsheet. The column is defined as DT_CY and it translates to DT_WSTR. How do I make the output be a numeric.On the xls, there is a little green marker. When I click on it does change the DT_WSTR to Numeric. I want SSIS to spool the correct format.Fri, 06 Nov 2009 14:52:33 GMTgreengianthttp://www.sqlservercentral.com/Forums/Topic814568-148-1.aspxHow do i see a contents of a package in SSIS without running it...I want to know which table or if there a procedure that it is using to transfer the data from one sql server 2005 machine to another??Any help will be appreciated..ThanksKarenThu, 05 Nov 2009 14:19:35 GMTKaren Roslundhttp://www.sqlservercentral.com/Forums/Topic814289-148-1.aspxHi.I am working on a projected to extract data from an SQL database into XML files. I have 4 stored procedures that return data based on a DATETIME parameter and a Text parameter that are common to all of the procedures. Each of the procures is constructed using the SELECT FOR XML PATH, ELEMENTS options. I have not used the TYPE option and I am not returning an XML variable in the stored procedures.Basically my SSIS package needs to work as follows:SET the current DATETIME into a variable: This I have workingSET the Text Parameter value into a variable: This I have workingUse these variables to run 4 stored procedures to populate some tables: Not attempted yet, secondary importanceUse these variables to run 4 stored procedures that each return XML string as described above. This is the bit I am stuck on. I have set up a Script Task to populate the variables and then in the Data Flow Task I have created a Script Component to tackle the first stored procedure, the code is as follows:Imports SystemImports System.DataImports System.Data.SqlClientImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.Wrapper<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _<CLSCompliant(False)> _Public Class ScriptMain Inherits UserComponent Dim connMgr As IDTSConnectionManager100 Dim sqlConn As SqlConnection Dim sqlReader As SqlDataReader Public Overrides Sub AcquireConnections(ByVal Transaction As Object) connMgr = Me.Connections.MyConnection sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) End Sub Public Overrides Sub PreExecute() Dim cmd As New SqlCommand cmd.CommandText = "EXEC [TFS].[p_UnitDetailFeed] '" & Me.ReadOnlyVariables("RemarketingCompany").Value & "', " + Me.ReadOnlyVariables("MyDate").Value cmd.Connection = sqlConn sqlReader = cmd.ExecuteReader End Sub Public Overrides Sub PostExecute() sqlReader.Close() End Sub Public Overrides Sub CreateNewOutputRows() Do While sqlReader.Read With Me.MyOutputBuffer .AddRow() .MyXML = sqlReader.GetString(0) End With Loop End Sub Public Overrides Sub ReleaseConnections() connMgr.ReleaseConnection(sqlConn) End SubEnd ClassThe next stage is a Flat File Destination to save the file.When I run the package I get the following runtime error:Script Component has encountered an exception in user code:Project name: Incorrect syntax near '/'I have no idea what is causing this as none of my code contains a / characterAll I can tell is that it is failing in the pre-excecute phase.Any ideas what is wrong?Thank youTim.Thu, 05 Nov 2009 08:17:39 GMTTim ffitchhttp://www.sqlservercentral.com/Forums/Topic781608-148-1.aspxHi,When I try to run a SSIS package that call another one, I am getting "The process cannot access the file because it is being used by another process.".I am sure I am the only one on the box that no one are using the package. Does someone already had the same issue? Do you know a workaround to fix this?ThanksWed, 02 Sep 2009 09:57:16 GMTRem-487422http://www.sqlservercentral.com/Forums/Topic759838-148-1.aspxI've been searching for free SSIS Documenter software, but haven't found any...Has anyone found any Free options?Sun, 26 Jul 2009 17:40:52 GMTdspohn23http://www.sqlservercentral.com/Forums/Topic815029-148-1.aspxI'm getting this error message when trying to create a new job.Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)I Googled it, and that lead me to this: http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspxWhich seems like the right answer, but when I'm trying to setup an account with the appropriate privileges, I don't have the required server roles "SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole" as options.What to do? Is there another/better way? This seems overly complicated for simply scheduling a *.dtsx package.Fri, 06 Nov 2009 09:46:18 GMTbhudson-1126665http://www.sqlservercentral.com/Forums/Topic813565-148-1.aspxHi All, This may be a bit of a stupid question but, I have a package I'm building to pull a bunch of data in from a multitude of files then do some some other bits of work on that data. The first section I am having issues with is an import from two .csv files, totalling about 70k records with 15 odd fields in each file (the layouts are identical).Baiscally in my package I have two flat file source objects, two sorts (to allow a merge), a merge, then its all piped into a destination object which is pointing at a table in my DB.When I run this package it starts ticking up the number of rows and after not too long my machine is all but un-usable because its so slow. Looking at my system resources it seems to eat all m y page file but the application itself seems to use no memory at all. I am assuming this is because ssis is loading all this data into my local memory or something.So my four questions are1.) Is my assumption about loading it all into memory correct?2.) Why does it do this?3.) can I stop it doing this?3a.) If not can I make it use my servers' memory instead or something?4.) Is there a quicker way of loading a bunch of files, all identical in format into a single table, in a way I can automate?Thanks!DaveWed, 04 Nov 2009 06:42:26 GMTdavid.morrison-1035652http://www.sqlservercentral.com/Forums/Topic815020-148-1.aspxI have some conerns with my SSIS package regarding the data validation. My package do the data validation according to the business rules to the files that we get from various clients.My concern is In my package as I am validating data with various rules the record that does not pass the valdation are kicked of out of the file. Now For example if I am validating my Column1 with some rule like It sholud not be NULL, after the validation is completed the record that pass the validation are sent to next task, and the records that does not pass the validation are redirected to error file with a error description column added to it. Now my problem over here is Once my 1st validation is done my second validation task will run and so on, but for each validation the rows that does not pass the validation rule will be redirected to the error folder , so in the next validation step I'll be not able to validate the second validation rule to the columns which are redirected to error folder in the previous validation task.U may be wondering why I need to validate the error rows for every validation rule as It coludnt pass even 1 validation, this is because we are going to manually go through the error file re-evaluate and make cooresponding changes to the error records if it is possible.So when the data analyst see's the error file, it checks the error message for that record makes the changes to the column in that record which couldm't pass the validation.So one record may be invalid for multiple vaidation rules.So, By using my package it is not possible to check all the records for all the validation rules because as the error records are kicked off for each validation task and remining records are passed to next alidation, I cannot perform all validation tasks on all the records......I thought of using multi-cast as a solution for this problems so I can send the same records to all validation task and merge them at the end and remove the duplicates. But I don't think this is advicable....So If any one has goe through this kind of situation or any one have any suggestions please help me....Ur help is highly appreciated.......Fri, 06 Nov 2009 09:37:19 GMTkarthika9http://www.sqlservercentral.com/Forums/Topic814961-148-1.aspxHi,I have a SSIS package that is located in the Stored Packages MSDB location.. I exported that package to a file system and then opened that package using BIDS...In bids I couldnt see much as to what are the tables or stored procedures that it is using to excute this package...But i found out that Source connection is ServerName-MAchineA.QM Database... and the destination connection'is Servername-MachineB. ReportCubes...So now if i have to look for a job in the sql agent should i be looking at Machine A or Machine B.. I do see sql job for this package but it has not been enabled...Is there any other way to check if this job was last run and where can i find it..Any help will be appreciated..Thanks,KarenFri, 06 Nov 2009 08:37:24 GMTKaren Roslundhttp://www.sqlservercentral.com/Forums/Topic463182-148-1.aspxIs anyone using any sort of version control tools for SSIS packages? If so, what are you using and how has it worked out for you?Thanks,TimMon, 03 Mar 2008 08:35:26 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic315036-148-1.aspxhow can i use the Global Variables in SSIS... in which DTS i use to use Global variables for even passing the Server Name & Database Names.Thu, 12 Oct 2006 10:20:00 GMTposhhttp://www.sqlservercentral.com/Forums/Topic783721-148-1.aspxI have an SSIS package which is running on a test server, querying databases on another server. There are 5 child packages in a parent package and whenever the job fails, returning the error below, it is on the same child package. Sometimes, the job runs fine when it is rerun straight afer failure. Is there a known reason within SSIS as to why this would happen sporadically? An OLE DB error has occurred. Error code: 0x80004005OnError,SYSBOSIDEVTEST2,NT AUTHORITY\SYSTEM,CBS Data Flow Task,{3F929A6D-F693-4CB2-BAD2-D17FC0BC4127},{539694EE-3213-4E87-A168-93CD5EBA71D8},06/09/2009 07:38:03,06/09/2009 07:38:03,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.".Mon, 07 Sep 2009 05:39:11 GMTrobin-892784http://www.sqlservercentral.com/Forums/Topic814814-148-1.aspxI am creating a SSIS pkg which fetches the data from DB2 and load it into SQL 2005 .Now while running it gives the following error...SSIS package "Package.dtsx" starting.Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.Error: 0xC0202009 at Data Flow Task, OLE DB Source [489]: An OLE DB error has occurred. Error code: 0x80040E00.Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (489) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1686)" wrote 0 rows.Task failed: Data Flow TaskWarning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.SSIS package "Package.dtsx" finished: Failure.AnyonePlease Help... Fri, 06 Nov 2009 04:17:06 GMTashish_mehrotrahttp://www.sqlservercentral.com/Forums/Topic482052-148-1.aspxhello, i m working on a SSIS package in which i m returning query results & trying to assign it to a variable with int32 datatype.database is oracle & m using the connection microsoft oledb connection for oracle.in sqltask my query isSelect max(record) from target here i hv taken resultset as single row type & assigning values to rowname 0 to var1 max(record) is of numeric type.but when i run the task its giving the error that [Execute SQL Task] Error: An error occurred while assigning a value to variable "va1": "Unsupported data type on result set binding 0.". please help me m sruggling alot for the issue.Tue, 08 Apr 2008 23:34:13 GMTsamita_sawanthttp://www.sqlservercentral.com/Forums/Topic814439-148-1.aspxI have two OLEDB data sources that I want to join together. Both sources use a SQL statement to get the data and sort it (order by). I have set the IsSorted property of both OLEDB sources to True and set the SortKeyPositions correctly. The Merge Join component sees that everything is order and I do my inner join selecting all the columns from Source A and the needed column from Source B. Of the the 300K records that go through the Merge Join only 400 are getting joined. I double checked the Order By in both SQL statements and they match. The SortKeyPositions are the same.Just to make sure that I wasn't going completely crazy I put a sort on each source. I sorted by the same columns. (SSIS was nice enough to let me know that I don't need to use the sort as the data were already sorted.) When I run the package now I am getting my 300K joins. :w00t:Just to make more of my hair go gray, there is another data flow task in the same package that uses the SQL statement in the source to order the records before doing a merge join and that works perfectly.I'm open to suggestions.Thu, 05 Nov 2009 10:56:12 GMTkristian.mulcahyhttp://www.sqlservercentral.com/Forums/Topic814707-148-1.aspxHello - thanks in advance for any help you can provide.I have a situation where I would like to run INSERT SQL commands conditionally. Meaning, if there is data in table A, insert them to table Z, otherwise if there is data from table B, insert them to table Z, otherwise if data in table C, insert them to table Z. I cannot simply execute the INSERT statements sequentially - they can only be run conditionally after first looking in table A, then table B then table C. The destination is always the same - table Z.I have been scouring the internet looking for ways to do this within a Script Task. Is this the right approach, to write a Script Task which looks for not EOF within tables before executing SQL? Is it possible to execute SQL statements within Script Tasks - I haven't found good examples of this either? I'm looking for a general approach here - it would help me a lot.Thanks!Thu, 05 Nov 2009 22:57:17 GMTstephen.j.thomashttp://www.sqlservercentral.com/Forums/Topic446498-148-1.aspxi want to append rows from excel file to sql server tableTable has a identity column. How do i achieve it thru SSIS or thru DTSWizard? I get error if i the identify column with null value in excel sheet.Wed, 23 Jan 2008 08:56:54 GMTkeywestfl9http://www.sqlservercentral.com/Forums/Topic814489-148-1.aspxHelp,I'm use the following to Creates a file name which is populated via a Stored Procedure and assi@[User::gsFilePath] +@[User::gsFileName] + @[User::FilePrefix] + (DT_STR,4,1252) MONTH (GETDATE() ) + (DT_STR,4,1252) YEAR (GETDATE() ) + Right("0" + (DT_STR,4,1252) DatePart("minute",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("second",getdate()),2) + @[User::FileSuffix]The File was created with the following name depending upon the time & date:F:\SQLB888-S01-BAK-F5a\MSSQLI\Backups\ETL\ABC_Reports\XXX_DD_J9\Reports\ReportsXXX_DD_J9_DailyCount_1120094607.csvMy problem is that I tried using the send mail to email this file as an attachment and I obviously can't because the expression property can't see the variable that I created in the Flat File Connection.How can I calculate the expressions at the package level so that the scope of the variables are not limited to the task?I wrote the script task listed to calculate the datetime in a failed attempt to write to the package variable. The Variable in VB is populated but the Dts.Variables("DateTimeStamp").Value = sDateTimeStamp does not write to the Package Variable. I have the Variable defined as ReadWrite Variable in the Script Properties. How do I write to the package variable?Also what is the syntax or how do you create an expression to concatenate multiple package variables into one , i.e. gsPathFileName?I would appreciate and input on this issue.Thank you,KentThu, 05 Nov 2009 12:23:21 GMTKent Krug-210111http://www.sqlservercentral.com/Forums/Topic814372-148-1.aspxIs there a way to pass in a job info to an SSIS package? Like the job instance ID or the Job Guid?Thu, 05 Nov 2009 09:37:22 GMTtvanharphttp://www.sqlservercentral.com/Forums/Topic811919-148-1.aspxI am new to SQL Server and it would be great if someone could help me. I am using SSIS Package to import *.log files into database. The format of the log files are yyyymmddXYZ.log.I am using a foreach loop as well. I want to know if there is way to import all the log files till yesterday. [b]Example[/b]: Yesterday: 10/29/2009, Today:10/30/2009. I want all the files till 10/29 excluding 10/30.I want to do this as i am getting an error "[Flat File Source [1]] Warning: The process cannot access the file because it is being used by another process." for the last (10/30) .log file.I think this is a valid message as there is another process that is updating "Today's file. All the other (older than 10/29) .log files are getting imported into database without any errors.Please Help !Fri, 30 Oct 2009 16:45:29 GMTPriya11http://www.sqlservercentral.com/Forums/Topic814379-148-1.aspxHi, I have a problem with SISS anche AS/400 when try to import dato from SQLServer table and AS/400 table.This is my error (italian ...)Any idea? Thanks!!Destinazione OLE DB [91]] Errore: Codice di errore SSIS DTS_E_OLEDBERROR. Si è verificato un errore OLE DB. Codice di errore: 0x80040E21. È disponibile un record OLE DB. Origine: "IBMDA400 Command" Hresult: 0x80004005 Descrizione: "SQL0104: Token . non valido. Token validi: <IDENTIFIER>. Causa. . . . . : E' stato rilevato un errore di sintassi sul token .. Il token . non è valido. Un elenco parziale dei token validi è <IDENTIFIER>. Questo elenco presuppone che l'istruzione sia corretta fino al token. E' possibile che l'errore sia precedente nell'istruzione, ,ma la sintassi dell'istruzione sembrava valida fino a questo punto. Correzione . . . : Effettuare almeno una delle seguenti operazioni e ripetere: -- Verificare l'istruzione SQL nell'area del token .. Correggere l'istruzione. L'errore potrebbe essere una virgola o apici mancanti, una parola scritta in modo scorretto oppure potrebbe essere relativo all'ordine delle clausole. - - Se il token di errore è <END-OF-STATEMENT>, correggere l'istruzione SQL perché non termina con una clausola valida.".Thu, 05 Nov 2009 09:43:24 GMTMaxRow72http://www.sqlservercentral.com/Forums/Topic813867-148-1.aspx[b]I am trying to download WSDL file from a web service task inside SSIS package. I have tested the http connection and it works fine, but when I try to download the WSDL file, I get the following error:Access is denied, (Exception from HRESULT: 0x80070005(E_ACCESSdenied)).I could not figureout what seems to be the problem. Can anyone help me with this?[/b]Wed, 04 Nov 2009 14:15:19 GMTcospowhttp://www.sqlservercentral.com/Forums/Topic813506-148-1.aspxHi All,We have a clinet that wants us to send him his files directly to his Web-Folder.the desired web-folder sits in an HTTPS protocole server.is there any way i can upload files to such a webfolder directly from SSIS flow (using script component maybe... ?).i did try connect to the webfolder fusing the script component with the following method:[b]Public Sub Main() Dim WebConnection As New WebClient() Dim ConnectionString As String Dim creds As New NetworkCredential("Username", "Password") ConnectionString = "https://Web-Folder/DataFolder" Try With WebConnection .BaseAddress = ConnectionString .Credentials = creds End With Catch ex As Exception Dts.Events.FireError(0, "Problem connecting to website: ", ex.Message, "", 0) End Try Try With WebConnection.UploadFile(ConnectionString, "c:\temp\tmp.csv") End With Catch ex As Exception Dts.Events.FireError(0, "Problem uploading file: ", ex.Message, "", 0) End Try End Sub[/b]i get an "Unauthorized" error.i guess that this does not supports HTTPS protocol.anyone?????....Thanks in advance.Wed, 04 Nov 2009 03:31:00 GMTboaz.goldsteinhttp://www.sqlservercentral.com/Forums/Topic813961-148-1.aspxI am trying to understand this. I have made an execute process task to unzip some of the files with .rar extension. I am specifying the file name and output folder in the command line argument section and it works pretty fine.Now, if I want to put it in For each loop container and pass the variable which I obtained from container to the execute process then how can I pass it? I have tried replacing filename with the variable name which does not work. Do I have to specify it at a different place or differently?Wed, 04 Nov 2009 19:47:34 GMTkunaal desaihttp://www.sqlservercentral.com/Forums/Topic813911-148-1.aspxI have a SQL Server SSIS FTP task in one of my jobs and this has been running for a while runs successfully but does not download file. I am not certain if certain upgrades were installed to SQL Server or .NET environment, but what is happening is that the FTP task succeeds, but the excel file is never downloaded. I have break points and go to the file system and check and no file is downloaded once the FTP task completes. This has been working in the past, but has been an issue since the last 3 or so weeks. Deleting and recreating the FTP task does not solve the problem. Any tips on resolving this will be very helpful.Thanks,MonishaWed, 04 Nov 2009 15:33:16 GMTM Shahhttp://www.sqlservercentral.com/Forums/Topic813782-148-1.aspxI'm at a loss to understand why I cannot convert a string variable to anything within Expression Builder. (DT_NUMERIC, 10,0) [USER::sCount] returns error code 0xc00470c2Can ANYONE please help me with this? If this does not work, what are my options for converting a string to number so I can derive a new variable (var1-var2 = var3) within SSIS? Wed, 04 Nov 2009 12:06:27 GMTmbokkerhttp://www.sqlservercentral.com/Forums/Topic813674-148-1.aspxHello, I have a bunch of SSIS Packages which have variables set for the Destination Connection and Source Connection. The variables are set within the packages themselves. Our DBA want us to make it so the variables for these connections are actually set within the agent job that calls the SSIS package so that when the DBA deployes our packages from UAT to production they only have to update the variables in the agent job to point to the production paths as opposed to having to update the SSIS package manually. Waht is the best way to accomplish this? I'm unsure how to make my package call a variable set by an agent job.Wed, 04 Nov 2009 09:25:01 GMTCode-1029433http://www.sqlservercentral.com/Forums/Topic813168-148-1.aspxI have a package set up to perform some automatic flat file generation from some DB table data.There are a couple of conditions I check for prior to entering the file generation process, each may cause a branch to a task that logs some detail to a SQL table. The steps then fall through to another task that sends out an email summarizing info from the SQL log table. Call it the email task.Pretty simple stuff. All the pathways work fine if they are the only ones with a precedence constraint to the email task. If I have more than one constraint path leading to the email task, the process goes green until that task, then stops. The Progress tab shows it as completed.If I remove the "other" constraints leading to the email task (there are three possible paths to get there), it works fine. i.e. each individual pathway works fine from beginning to end.BTW, I call it the email task, but I tried putting in an intervening task (empty script task) upstream from it just to allow the paths to join together, with the same results.Any ideas on this one?TnxTue, 03 Nov 2009 12:53:46 GMTgary.woodhttp://www.sqlservercentral.com/Forums/Topic813097-148-1.aspxHi guysI have an interesting problem that has left me confused. I am uploading files to an FTP site using variables in a foreach loop. The for each loop is working fine, but the actual ftp load isnt. The error i get get is the destination path is incorrect, yet it works if i were to manually navigate there using IE. I know that when logging into an FTP dirtectory you actually logging into a default directory set up for the username and password supplied. (grasping at straws i know..) is it possible that when SSIS logs in using these credentials it sees the full path that i cant see in IE, giving me ? I have done FTP tasks through SSIS in much the same way before, this this problem makes no sense. Any help welcome;-)Thanx.Tue, 03 Nov 2009 10:40:48 GMTMark Kinnear