InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 00:55:13 GMT20http://www.sqlservercentral.com/Forums/Topic1369354-364-1.aspxHi, I'm a relative noob at this - so please bare (bear?) with me.I am retrieving XML data from a web service and I want to put this into a database for reporting purposes. I currently use VS2008 to edit the SSIS package. I would use VS2010 or VS2012 but neither of them seem to let me use a URL in the "XML File Source" data flow task. Anyway, when I put the URL into VS2008, I ask it to generate its own XSD - which it does. But what it does is split the XML up into what looks like tables. It also seems to generate its own ID field for each of the tables. I could probably use this as the unique key to join the 'tables' together. [b]BUT[/b], I can only download 12 months of data at a time - before it times out. So I created multiple instances of the XML File Source import - each specifying a different year in the URL. But it looks like the generated ID is reset to zero each time I run the XML File Source import - thus resulting in duplicate ID's. So, the self-generating ID's (if that's what they are) are no good to me.I was looking at trying to manually modify the XSD...or use XSLT transformations....or importing the XML into SQL as is and dealing with it there - but my SQL isn't super dooper either.Can anyone offer any advice please? Either specific to the issues mentioned above - or about my whole approach.Much appreciated.Regards,BryanEDIT: SQL2008Fri, 05 Oct 2012 18:07:09 GMTbryan.gilberdhttp://www.sqlservercentral.com/Forums/Topic1456544-364-1.aspxHi,So, after some wrangling I was able to get an instance of SQL 2012 set up, and I followed along this (great!) article on creating a watched drop folder for files: (http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/91665/). That was my first use of SSIS, so hang onto your seats.I'd like to adapt that process to my situation. I don't maintain many long term tables. Most are used for less than 3 months (many for much shorter), and then sit around collecting dust while waiting potentially for another project that might need to be checked against them for duplicate data. We have a standard template of about 100 fields that I can usually map data to (name, phone, email, address, etc.), but we get a lot of extra fields that I have to tack on to any given table, and fields are often added or subtracted from file to file, depending on whim and fancy, as best I can tell.Most of the processes for this were created by a guy who left the company, and I sort of got pushed onto the SQL grenade shortly thereafter with no real exposure to SQL previously aside from helping end users with login or connectivity issues. Anyway, here's what the general gist of what I'd do in an ideal situation:1) have a script that watches a dmailbox for emails with file attachments that would distribute the fileto the appropriate folder based on project number in the subject line, which is (for now) always a j followed by 7 digits. orHave PM/analysts drop files in appropriate folders (sort of takes away from the mystery and magic of it all, though).2) SSIS bring the files in, maps existing headers, tacks on any new headers, and ignores any not included. Then it runs our stored procedures, and any one off syntax I add in to build variables, etc.3) Reply all with email of requested counts of sample contents4) Goes back to waiting for a fileI would appreciate any advice on process or method to get this running. Let me know if you need more information about anything. I'm not asking anyone to do it for me, but I'm so green I have Kermit's sympathy. Thanks!Fri, 24 May 2013 08:40:09 GMTerikdhttp://www.sqlservercentral.com/Forums/Topic1456051-364-1.aspxGreetings, I have been tasked to investigate how to develop an SSIS solution that would accept a string variable containing n-times Datasource SQL statements, and a string variable containing n-times Excel and/or Access destinations.What they would like it to do is parse out the individual statements and destinations from the variable values, and then preform the export based on that pairing. After that completes, they want to loop back through the process and move on to the next pairing. Example:[u]Variables:[/u][b]@SourceSQL[/b] = 'SELECT OrganizationName, Address, City, State, Zip FROM dbo.Organizations † SELECT GroupName, GroupType, GroupDivision FROM dbo.Group † SELECT ServiceName, ServiceType, ServiceLocation FROM dbo.Services'[b]@Destinations[/b] = 'Orgs_Sheet † Groups_Sheet † Service_Sheet' This needs to be either written to generate via C# code, or as a generic package that we can call from the application. My question...is this possible? And if so how?Thanks in advance for the help.KJThu, 23 May 2013 09:19:30 GMTKJKKPSIhttp://www.sqlservercentral.com/Forums/Topic1456301-364-1.aspxHi,I am trying to create a package that loops through several excel files to import data into a database. The problem I am experiencing is that as soon as I assign an expression variable for connection string in the Excel connection manager, I get the following error: Error 1 Validation error. [xyz]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "[XYZ]" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. I'm using ssis 2012. I have set DelayValidation to true on both data flow task and excel connection manager. I have applied my variable to both ConnectionString and ExcelFilePath but this error persists:Please assist.Thanks :-DFri, 24 May 2013 00:11:00 GMTthamilangahttp://www.sqlservercentral.com/Forums/Topic1454400-364-1.aspxHi All, I'm new to SSIS world. I have some doubts as follows.1. How many packages can be created in single SSIS Project ?2. How many control flow tasks possible in single package in SSIS?3 . How many dataflow task possible in single package in SSIS?4. How many Source Connection,Transformation and Destination Connection possible in dataflow task of the single package in SSIS?Thanks in Advance...Waiting for your valueable answers.Regards,T. AnandhakscSun, 19 May 2013 22:33:35 GMTanandhakschttp://www.sqlservercentral.com/Forums/Topic1453185-364-1.aspxI have a ssis package with foreach loop.The connection times out after some 50 files for processed when the loop is executed and it gets times out saying below error.The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.". Possible failure reasons: Problems with the query, "ResultSet" property not set correclyCan somebody please tell me what could be the issue and how to resolve?Wed, 15 May 2013 09:33:55 GMTRahul Bhosalehttp://www.sqlservercentral.com/Forums/Topic1451626-364-1.aspxHey Everyone, :-)I am creating an SSIS package and having some issues removing leading 0's from a field. I cannot change the data type to int since the values can contain letters and numbers. Does anyone know how to remove leading 0's for vchar data without removing all the 0's from the data? Any help would be greatly appreciated.Fri, 10 May 2013 08:34:31 GMTjjgierhttp://www.sqlservercentral.com/Forums/Topic1454466-364-1.aspxHi,Im starting a new project today. What I have is an area with 10 folders where services drop their files to be sent to schools.What I'm wanting to do is log all these files in a table in SQL Server.I have created a variable with each directory in which will hopefully feed into a For each loop container.So the first directory is checked. Each file name is recorded. Id love to grab any information I can about the file. File Type. Size etc. Anything that I can log.Once its recorded all the file names I want it to move onto the next folder in the variable.Could anyone point me to any good documents on how to do this. Im normally dealing with for example XLS files and using a loop to add all the data into SQL. This is new to me!DebbieMon, 20 May 2013 04:01:58 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1454695-364-1.aspxWe would like to do this in SSIS package:1. pull a list of employeeID, with their address from a SQL server table using select query (we can use Execute SQL task)2. Using above record set, to update the same employees in an oracle database that has a stored procedure that we can pass employeeID, and it will match employeeID to update their address.Can no2 be done using SSIS? can we call a stored procedure in Oracle?Thanks,Mon, 20 May 2013 13:03:41 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1103535-364-1.aspxCan anyone out there recommend an SSIS task that can go into extract a file from an email attachment? We are using Exchange as our email server and SSIS 2008.Wed, 04 May 2011 15:47:25 GMTimani_technologyhttp://www.sqlservercentral.com/Forums/Topic1454448-364-1.aspxHiIs there a way where I can expose the vb.net/c# code used in Script task to external application? I mean if I have an application which plan to use code of script task, will it be possible?thanksMon, 20 May 2013 03:08:50 GMTS_Kumar_Shttp://www.sqlservercentral.com/Forums/Topic1455091-364-1.aspxHi All,I have a package that does the following:1. Truncates the staging table (in db)2. Data Flow Task that connects to a csv and transfers from csv to staging table3. Execute SQL Task to update the staging table in order to transfer data from staging to real/actual tableIt's pretty basic and I don't have any issues regarding those steps. However, there are two .xlsx files that are in a folder called Source_Original that need to be moved to a folder called Source_Archive. At the end of point 3 located above is where I am attempting the move using a ForEach Loop container and a File System Task. For the Loop Editor Variable Mappings, I use a blank Variable User::FileName index 0. In the Task Editor, the DestinationVariable is User::FileDestination and the Source Conncection is the variabel User::FileName. FileDestination is predefined according to our UNC Path.At any given time in Source_Original folder there can be either one or two or both files called (and this is where I am lost):1. NB_Can__US_Request_Annual.xlsx2. NB_Can__US_Request_Qtly.xlsxHow can I set this up to move one, two, or both files? In the File System Task Editor, the SourceVariable cannot be explicitly defined as a connection and I need this part dynamic but am unsure how to go about this. Hopefully I am being clear.Tue, 21 May 2013 09:15:10 GMTMordredhttp://www.sqlservercentral.com/Forums/Topic1453840-364-1.aspxhi all,my excel source file contain some data name as coordinates i want to load it into sql server table the col data type is Geometry but it is not loading i think ssis does not support gemeotry data type how to load data into oledb destination is there any process in ssisThu, 16 May 2013 22:24:08 GMTscheppallihttp://www.sqlservercentral.com/Forums/Topic1454488-364-1.aspxHere is the 2nd task I want to doI have 10 folders from 10 services. Each of these services can drop any file into their own folder. The Files MUST start with 4 numbers, e.g. 1097.These files need to be copied into the corresponding Number (So a folder named 1097)Ive got 400 of these numbered folders.I have a SQL Script creating the variable for each folder.I want to feed this into a file task which goes to that folder. Splits the files into the specific directories based on LEFT(4,FileName) If they have got it wrong its added to a misc folder.Is this do - able in Integration Services?SO source Folder File Destination FolderService VA > 2300_VA_Example_File.xls > 2300 > 2311_VA_Example_File.doc >2311Service MA > 3456_MA_Example_File.csv > 3456 > 2300_MA_Example_File.ppt > 2300Mon, 20 May 2013 05:16:38 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1452649-364-1.aspxHello All,I have a package that downloads a lot of csv files and transfers their contents into a database. For one of the tasks in this package, I have a Precedence Constraint that evaluates the following expression:[code]@Table_Number == "3290056" || @Table_Number == "3290057" || @Table_Number == "3290058" || @Table_Number == "3290059" || @Table_Number == "3290060" || @Table_Number == "3290061" || @Table_Number == "3290062" || @Table_Number == "3290063" || @Table_Number == "3290064" || @Table_Number == "3290065" || @Table_Number == "3290066" || @Table_Number == "3290067" || @Table_Number == "3290068"[/code] Basically what happens up to this is:1. Download data based on a tblRelease_Dates query (if release is today, download).2. Unzip the file (extract the csv)3. Set the final destination path to archive the zipped files.4. Start transform and load processes.My problem is when the process flows get to the above precedence constraints. The previous line I gave checks which table is being transferred and loaded. However, I need to extract from the file in the folder with which-ever Table_Number is called. In the next step, I need to connect to the csv files and then load the values into a staging table. In the folder, the file name looks like: 3290056-eng.csv, 3290057-eng.csv, ..., & 3290068-eng.csv. If I do this the way I've learned, which is to create a connection using the connection manager, it will not be a dynamic connection so no matter which table number I'm on, it will only connect to the connection I make. How can I create a dynamic connection based on the table numbers. I'm going to play around in the Script Task sandbox to try and figure this out but I thought in the meantime I would ask first.Tue, 14 May 2013 08:55:04 GMTMordredhttp://www.sqlservercentral.com/Forums/Topic1454868-364-1.aspxDear All, i have task to copy all data from source table to destination table (30 table), so i keep source table name and destination table name in database and use sql statement to get each source table name and destination into variable and use table variable to source table and destination table then I got error VS_NEEDSNEWMETADATA. I try to set delayValidation = TRUE at data flow level and ValidateMetaData = False at Data Source level but error still happened Tue, 21 May 2013 02:23:57 GMTwaraleeshttp://www.sqlservercentral.com/Forums/Topic1453386-364-1.aspxHI ,I have a Dataflowtask in which following following Transformations are used---OLEDB Source[Pulls Data From X StagingTable]---Balance data distributor is used [For Parallel Execution]---CRM Destination is used for Inserts/Updates data into Y Target Table---Conditional Split[On >0] exists. Case 1 [>0-Error]:Updates Table X's Row as Unsuccesful Case 2[<=0-No Error]:Updates Table X's Row as SuccesfulWhile Peforming above scenario Iam getting Dead Lock issue ."Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".I belive this is due to Update operation performed on table Kindly request you to help me in this regard.Thu, 16 May 2013 03:31:57 GMTgreeshatuhttp://www.sqlservercentral.com/Forums/Topic1454165-364-1.aspxI have a parent package that runs a series of child packages. In the parent package, I have a connection manager entry for each child package, and in the connection string property is the hard-coded file system location of that package.The problem is, these child packages are located one place when I am changing and testing them, and a different location for production. What I'd like to be able to do is create an expression for the connection string that resolves to [i]directory location of the parent package + child package name[/i].In other words, my parent package and child packages are in the same directory: c:\TestProject. So what I want as the connection string for the child projects is c:\TestProject\Child1.dtsx, where c:\TestProject resolves from [b]c:\TestProject\[/b]Parent.dtsx.I am building these in BIDS 2008.Is this possible, and if so, how?Thanks,MattieFri, 17 May 2013 12:25:13 GMTMattieNHhttp://www.sqlservercentral.com/Forums/Topic1451787-364-1.aspxI've built a SSIS package using SSDT and VS2010 that manages partition creation and processing for a cube in Analysis Services (SSAS 2012). It processes the dimensions as well.My cube is partitioned by workweek. Within a Script Task I'm using AMO to connect to the AS server and determine if a new partition is needed and if it is then I update a couple XMLA scripts with the new partition name and use them later to create (using an AS execute DDL task) and then process (using an AS processing task) the new partition.When I run the package from my workstation (W7 64bit) it runs fine. However, when I deploy the package to the catalog on our SSIS server (SSIS 2012) and run it the package fails. The only error I had initially was "The script returned a failure result.". Not very helpful. After adding some additional logging code I determined the problem is occurring when I try to connect to the AS server:Microsoft.AnalysisServices.Server objServer = new Microsoft.AnalysisServices.Server();objServer.Connect(ServerName);The error I'm getting is "A connection cannot be made. Ensure that the server is running.". I have tried mucking about with the connection properties but to no avail. I have run it directly from the SSIS catalog with no luck. I ran it using a SQL Server Agent job (because the agent process runs under a network account that I know has access to the AS server) but that didn't work either. I created a proxy on our SSIS server and used my Windows credentials (I'm an admin on the AS server) to execute the package but sadly no connection. The SSIS 11.0 service is running under the NT Service\MsDtsServer110 account (I think this is a virtual account created by the install). I watched the server processes as I ran the package from the catalog and it looked like the SSIS service spawns another exe called ISServerExec to run the package. Surprisingly task manager said that ISServerExec was running under my Windows account but as I mention above it did not connect.I know it's possible to connect to AS from a package running on the SSIS server because prior to adding the partitioning code I was simply processing the cube dimensions and a single fact table partition using two Analysis Services Processing Tasks and they ran without any problems. As a matter of fact, I added the partitioning code to the same package so I know my Connection Managers are good. This issue only occurs when using the Script Task and AMO.I need help! I have spent all day banging my head against this problem and I'm starting to get dizzy. Any information/suggestions would be greatly appreciated.Thanks,Chris.Fri, 10 May 2013 17:20:53 GMTcrsundstromhttp://www.sqlservercentral.com/Forums/Topic1453824-364-1.aspxAs the title suggests...Is it possible to use a SSRS shared dataset as a data source for SSIS? I need to bring data from a third-party, hosted system into our warehouse. They currently make their data available through a SSRS portal, with all the key data published as shared data sets. I can build custom reports, using the shared data sets and then suck the reports in our SSIS environment (using some of the XML tools). But logically, the report seems superfluous (since it's a presentation layer, and I'm actually interested in the data).Thu, 16 May 2013 20:24:22 GMTnick.mead 87957http://www.sqlservercentral.com/Forums/Topic1453544-364-1.aspxI am trying to import a text file into a SQL Server table. My package runs fine and imports the data into the table, however I am finding that it is not importing 1 row. For instance the total number of rows processed is 56610 and the SSIS Pipeline Information wrote 56109.Does anyone know how I can resolve this, so it imports all the rows.ThanksThu, 16 May 2013 07:49:32 GMTdbmanhttp://www.sqlservercentral.com/Forums/Topic775159-364-1.aspxI get this error if i try to import a tab delimited flat file to a table in sql server.[i]The column "Column 2" cannot be processed because more than one code page (65001 and 1252) are specified for it.[/i]I have set the "alwaysUseDefaultCodePage" property of the destination connection to True.There is no such property for the flatfile source connection.I have gotten around it by setting the codepage property of the OLEDB connection to 65001 but i dont know what this did or why it worked.What does this error mean?Is it a bug or is it serving a usefull purpose?Why did my "workaround" work?Thanks!Fri, 21 Aug 2009 09:17:47 GMTwinston Smithhttp://www.sqlservercentral.com/Forums/Topic1451614-364-1.aspxWe're currently working on building out a new data warehouse and I'm looking for resources or best practices on where SSIS is should ideally be installed. My thoughts are as follows, but I'm really looking for a best practices document or other documentation that outlines any other architectural gotcha's for this setup.1. On the same server as the data warehouse databasePros: No additional licensing cost Lower network latency, since it just needs to pull from the sources Lower hardware costsCons: Will contend with or lower the amount of memory SQL Server has at it's disposal for proc and buffer cache (this is big deal my this case) Will contend with CPU (CPU is typically very low on this server, so not such a big deal)2. On a separate stand alone serverPros: SSIS won't contend for resources with the database the DW serverCons: Higher Network latency Higher hardware costs Higher licensing costsFri, 10 May 2013 08:12:21 GMTCrosanhttp://www.sqlservercentral.com/Forums/Topic1450866-364-1.aspxI have used SSIS a while ago where my requirement was to create a new table out of the excel sheet and name it as master table. I have updated the values of other tables using this master table and automated the whole process using sql server agent Jobs. But Now its a whole different scenario where I have an excel sheet in my local drive and I have my database in one of our test servers.. So Now I should use SSIS to map the columns of the excel sheet with the different existing tables in the database. I am little confused how I am supposed to map different columns from one spreadsheet to differnt columns of different tables..EXAMPLE:I have a database 'DATABASE'I have tables TABLE1, TABLE2, TABLE3and the spreadsheet SSSo My columns S1, S2, S3 in the Spreadsheet SS are matched to the columns C2 in TABLE1, C5 in TABLE2 and C1 in TABLE3. There is a relationship between all the tables though.. Like C3 is common in all the above tablesMy job is to update all the columns accordingly with values in the excel sheetPlease let me know if i can accomplish the above criteria using SSIS and if so, any ideas???Wed, 08 May 2013 20:50:26 GMTprathibha_aviatorhttp://www.sqlservercentral.com/Forums/Topic1449133-364-1.aspxHi Folks,I have a package that loads records in a CRM destination using a Web Service. This execution takes around let´s say 3 hours. I would like to know what would happen if new records are inserted in the source table during the package execution?Supposed that there`s no filter in the query that gets the source records. I can program the loading process in another way, I know. But what I really want to know is what would happen in this scenario? Are the new records loaded? Maybe depends on the buffer, if all the records were already catch when the new records come in?EDIT: "suppose that the OLEDB source that I'm using is still in Yellow when the new records arrive"Any comment would be appreciated.Regards,Fri, 03 May 2013 05:12:14 GMTPaul Hernándezhttp://www.sqlservercentral.com/Forums/Topic1451751-364-1.aspxSorry if this is the wrong section.I have a report that I have created that contains one parameter called AgentEmail.This report generates a list of customers which our support crew are going to call, however, each customer is assigned to a certain support agent by using the agent's email. The support person's email address is part of the report, which is why there's a parameter called AgentEmail.I want to create a SSIS package where calls the report, fills in the AgentEmail parameter, and then emails the agent with his report with his list of customers.Hope my explanation was clear.I've searched and searched, but really haven't found anything that helped. I do find some results were they say to use the ForEachLoop, but directions are terrible.Thanks for any help provided.Fri, 10 May 2013 15:26:58 GMTDarthBurritohttp://www.sqlservercentral.com/Forums/Topic1452131-364-1.aspxI'm currently creating a flat file export for one of our clients, i've managed to get the file in the format they want, i'm trying to get the easiest way of creating a dynamic file name. I've got the date in as a variable and the path ect but they want a count in the file name. For exampleFile name 1 : TDY_11-02-2013_{1}_T1.txt. The {} being the count. So next weeks file would be TDY_17-02-2013_{2}_T1.txt and so on and so forth.I cant see an easy way of doing this!! any idea's??Mon, 13 May 2013 08:28:36 GMThewitg 82815http://www.sqlservercentral.com/Forums/Topic1452088-364-1.aspxHi guys, this a two-pronged question.I have an SSIS package that needs to run 24/7 as it uses Kosans File Watcher to listen for new files into a directory. When the files are processed the package returns to wait mode. The file location is specified in a config file and differs between dev, test and production. Our packages are deployed using file location and xml config file rather than database storage and/or environment variables.Data services have set this up as an on-demand (i.e. non scheduled) SQL Agent job to call the package. HOWEVER...They have not set the agent job with the /CONFIGFILE switch to change the config file location BUT... the files are being correctly processed.So... I think there is another copy of the package already running which was started from dtsexecutil or a command line WITH the /CONFIGFILE . One instance is running but not finding anything because it is looking at the wrong directory and the other one is running outside the control environment and is doing all the work.I know that if the package is started from SQL agent or from xp_cmdshell you can see it in dtslog90 but does anyone know how to detect it if started from outside the SQL environment.Ultimately I want to do 2 things:1) the SQL server agent job should check for any running instance of the package and only start it if it is not started2) the SQL server agent job should relinquish control of the package (i.e. the job ends). In other words the Agent is responsible for starting the package rather than running the package.If anyone can help I would be very gratefulObironMon, 13 May 2013 07:12:36 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1258300-364-1.aspxHi , I have to transfer file from sftp site to my local server on E:\ecol\ . I have used 3rd party tool WinSCP and created .txt file. My .txt file is working . I took execute process task to execute .txt file but it is throwing an error [b]invalid access to memory[/b]. Please correct me if I have given incorrect path or arguments . [b]script for .txt file [/b]: option batch on option confirm off open sftp://user:password@IPAddress:Port cd ER_Reports\OHE\ option transfer ascii get AIO_Activity-en-us.csv E:\ecol\ close exit [b] path for WinSCP [/b]: C:\Program files(x86)\WinSCP\WinSCP.com [b]path for .txt file [/b]: C:\Users\Jpatel\Batch files\ecollege.txt I am not sure about working directory. [b] Configuration of Execute process task : Requirefullname : True Executable : C:\Program files(x86)\WinSCP\WinSCP.com Arguments : -script=C:\Users\Jpatel\Batch files\ecollege.txt Working Directory : C:\Program files(x86)\WinSCP StandardInputVariable : StandardOutputVariable: StandardErrorVariable: FailTaskIfReturnCodeIsNotSuccessValue : True SuccessValue : 0 TimeOutValue: 0 WindowStyle : Normal[/b] Please suggest me the solution where I have given incorrect information in the configuration. Thanks, Jasmini Mon, 27 Feb 2012 08:43:05 GMTjasminipatel51181http://www.sqlservercentral.com/Forums/Topic1451163-364-1.aspxWe are migrating to 2012 and all is going well except for an SSIS issue.We nightly download a financial file from an external provider. An SSIS package then loads this into a table using a flat file source. Nothing too sophisticated.This works fine under 2005 but have hit a problem in 2012, getting the error:[Source File [116]] Error: The column delimiter for column "Description 2" was not found.[Source File [116]] Error: An error occurred while processing file "\\xxx\yyy\ myfile.csv" on data row 41935.On further investigation of the file we can see the row has a double text delimiter (“) inside the column separators (,) (just prior to AAA below)."56371","03-DEC-2010","22-JUN-2010","03-DEC-2012","ABC","PQR","ZZSWAP",""AAA Frz 15Oct@5 % PPP 22/SEP/2011", “CREQWE"Researching this, it appears there are changes in this area for 2012. This is causing us big issues as we have no ability to avoid this in the source. The format is delimited, delimiter is comma and text qualifier double quote.Anyone any ideas how we can modify the package so it handles this like 2005 please?Thu, 09 May 2013 08:40:24 GMTken_englandhttp://www.sqlservercentral.com/Forums/Topic1451263-364-1.aspxI have an FTP command file that I want to open in a data flow and then using a Script Component find the "get" command and dynamically assign a variable value for the <filename> that is to be downloaded from the FTP Site. Problem seems that I cannot use the same actual flat file for Source and Destination. So I guess that I have to have two files a "script template file" for source and the actual "script file" for destination? Is this really the best way? Better alternatives?Thu, 09 May 2013 10:39:49 GMTkurt.whaleyhttp://www.sqlservercentral.com/Forums/Topic927634-364-1.aspxWhen working with SSIS and source or destination metadata changes SSIS does not reset the metadata it deletes the changed column and adds a new one at the end of the list. This results in messy packages after several changes have occured. To reset the metadata on data sources, change the source to a table or query that has no matching column names and then view columns in the data source. This will delete and recreate the metadata. Then swith the source back to the changed source you are trying to refresh metadata for and view columns again. This will completely recreate the metadata for the source. For destinations, change the destination to one with no matching column names and then view mapped columns. This will delete and recreate the metadata. Then switch back to the new or changed destination and view mapped columns. This effectively resets the metadata. Hope this is helpful.Tue, 25 May 2010 10:13:39 GMTLane Tharp-342944http://www.sqlservercentral.com/Forums/Topic1446571-364-1.aspxHi All, I am creating packages that import xml files.however I want to import files that are more than 213kb in size. I have a fore each container which picks up the files.Is there any way in ssis to check for the file size before importing?Any help will be highly appreciatedThanksYarhadThu, 25 Apr 2013 09:30:17 GMTYarHadhttp://www.sqlservercentral.com/Forums/Topic1449907-364-1.aspxBasically I am recieveing multiple flat files of two different file layouts, the file names do not give a clue to the layout, howevet the files (pipe delimited with different numbers of columns) have the column names in the first row and the first colum name is different for each layout.So Im looking for an easy way to read the data ( in a ascript ?? or powershell ??) and set a vaiable in my package to identify the data flow I need to use.SSIS 2012.Mon, 06 May 2013 15:29:12 GMTpmcquirk 96901http://www.sqlservercentral.com/Forums/Topic1450806-364-1.aspxHi all--We are going through the process of rationalizing our databases from SQL Server 2005 to 2012. In our development environment, however, we have many packages in SSIS 2005.We plan on upgrading all of the packages from SSIS 2005 to SSIS 2012, but given the volume of packages necessary and certain standards we have to abide by when testing and deploying packages, it will take some time. (must be upgraded on local, then deployed to dev, then staging...)My question is this: If I upgrade the engine and databases from SQL Server 2005 to SQL Server 2012, will my SSIS 2005 packages still execute properly with the SQL Server 2012 engine until we upgrade all of the SSIS packages from 2005 to 2012? Are there any caveats I should be aware of?Thanks in advance!Wed, 08 May 2013 14:55:59 GMTataldershttp://www.sqlservercentral.com/Forums/Topic1450735-364-1.aspxI am trying to familiarize myself with SSIS logging to SQL Server, and there is one error I'm having trouble getting past. I'm using the same OLEDB connection that I have set for my destination, and I have verified that the sysssislog table and the sp_ssis_addlogentry stored proc have been created. When ever I try and debug the package, though, I get the following:[center]Error at Package [Log provider "SSIS log provider for SQL Server"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "There is already an object named 'sysssislog' in the database.".[center]This error is repeated several times, but I have no idea what to do with it. Is there a way to get it to validate the existence of the table before executing?Wed, 08 May 2013 12:18:48 GMTMark Harleyhttp://www.sqlservercentral.com/Forums/Topic1450505-364-1.aspxHi. We run a STD2008 64 bit relational engine SP1 in production. We recently migrated a pkg that reads an xlsx excel file from a separate server and got the following error.Error: 2013-05-07 13:04:16.65 Code: 0xC00F9304 Source: xxxxxxxx Connection manager "Excel Connection Manager" where xxxxxxxx is the name of a sub package. Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.A long time ago when the project started we followed the instructions at this link to install a provider on the target production machine (where the pkg would run) http://www.microsoft.com/en-us/download/details.aspx?id=23734 instead of installing excel there. This was in anticipation of this pkg's needs. I believe the goal was to have a 2007 excel connector for ssis. I believe we forgot to test this plumbing when the project switched hands.From our network guy's docs, it looks like an app called AccessDatabaseEngine is what got installed as a result and I'll be checking soon with him to learn if it somehow was uninstalled. I checked the solution and noticed that properties-> debugging->run64bitRunTime has been set to true all along during testing on my 32 bit client machine where excel 2007 is installed. The pkg runs from a command line in production. I have to check if excel 2007 is installed on the server from where I get the excel file. That may be just a file server and I'm not sure if an excel installation (or connector installed there) there makes a difference to ssis or not. I'm suspecting no. That actually opening the excel file in an ssis pkg takes place on the ssis server.I'll look up the error but am posting here in case the community knows where we went wrong and if we have the option of remaining 64 bit.--more infoI see one workaround at http://sqltechconsulting.com/2010/03/29/error-0xc00f9304-the-excel-connection-manager-is-not-supported-in-the-64-bit-version-of-ssis/ but that worries me considering I want to take full advantage of a 64 bit version . It suggests that the command line point at location “D:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe” /f “D:Test.dtsx” /X86 . I think the author meant . D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn . I'll try to look up the meaning of the trailing character /X86 but if the community knows whether or not i need it, please let me know. Also, from other posts it appears the debugging 64 bit flag is only relevant in design mode. Also, it appears that the x86 directory does also exist on my target server. Currently my .bat file points at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec" which I understand is the 64 bit executable. Also, I read something somewhere that even if you run the 32 bit executable, being on a 64 bit machine can still pose problems. Any straight answers would help.I do see at http://www.mssqltips.com/sqlservertip/1812/dtexec-command-line-parameters-using-command-files/ that the /x86 param is ignored when run from the command line.Wed, 08 May 2013 05:29:57 GMTdb042188http://www.sqlservercentral.com/Forums/Topic1450599-364-1.aspxHello,For months I've been trying to run a job (using the SQL Server Service Account) that calls a batch file on a remote server which in turn calls Gpg4win to encrypt a file. (I have been putting the project down and picking it up.)I haven't been able to get the job to call a SSIS package that calls the batch file (using an Execute Process task) under the Service Account credentials. The job always hangs. (I'm pretty sure there is no "Y/N" dialogue that is holding things up. When I run under my credentials, no dialogue box appears.)In an effort to get something to work, I've been trying to create a job under the Service Account that calls the CmdExec. I have managed to get the service account to create a test file with this DOS instruction:dir "\\myServer\myShare\*.csv" > "\\myServer\myShare\Test_file.txt"Believe me, I was happy to see the Service Account actually do something.Now I wish to expand the process like so:pushd "\\myServer\myShare\"dir *.csv > Test_file.txtpopdHowever, the CmdExec for SQL jobs doesn't appear to do anything with "pushd" or "popd". When I execute the commands directly above the CmdExec job succeeds, but I have no idea where the output file went. It isn't on the remote server (i.e., \\myServer).Does anyone know how to get a job running the CmdExec to change the current directory to a remote server? All help appreciated. Thanks!Wed, 08 May 2013 08:19:37 GMTcafescotthttp://www.sqlservercentral.com/Forums/Topic1450526-364-1.aspxI have execute my stored procedure using a select statement like Exec SP where select * from view,wt is the finall out put of this query can some one tell me the answer,,And one more thing is have to manipulate the Temprory tables,Is it manipulate the temprory tables ,but how some one tell me the answer with an exampleWed, 08 May 2013 06:40:28 GMTnareshmsbi13http://www.sqlservercentral.com/Forums/Topic1450453-364-1.aspxHw to import or export system varibles into an xml or csv file ,if any one tell me if it is possible or not in the package levelWed, 08 May 2013 02:49:09 GMTnareshmsbi13