Problem With Bulk Insert Task In Foreach Loop
Dec 18, 2007
Hi Guys
The problem is...
When i try to bulk insert a single file its working fine. When i want to loop a set of files in a folder and use Foreach Loop and BulkInsert Task...its failing..
In the flat file connection When i specify usage type as existing file...its loading the same file "n" number of times where n is the number of files in the folder.
When i select usage type as existing folder i get error " Cannot bulk load because the file "....folder" could not be opened. Can someone help me out with this?
I have sql 2005 as as separate instance...beside my 2000 which is default
Thanks!
View 9 Replies
ADVERTISEMENT
Feb 22, 2008
Hello all,
I am running into an issue that seems to be collaborated here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188886&SiteID=1.
To summarize, I have a Foreach Loop that uses a Foreach File Enumerator. The loop writes the file names to a variable which is used in the Expressions property for a flat file's ConnectionString. The flat file is used as a source destination in a Bulk Insert Task that is inside the Foreach Loop. The flat file's connection string is not picking up the file name, resulting in this error: "The specified connection "test.txt" is either not valid, or points to an invalid object. To continue, specify a valid connection."
It doesn't work for a file connection manager or a flat file connection manager.
It does work if I replace the Bulk Insert Task with a Data Flow (flat file source -> ole db destination). It also works if I set the file's connection string manually in a script task.
It looks to me as though the Bulk Insert isn't calling whatever method in the connection manager that reevaluates the property expression. Am I missing something or is this a bug? I looked at the connect site, but couldn't find this particular scenario.
Thanks for any insight you might provide!
Jessica
View 3 Replies
View Related
Sep 10, 2007
Hello All
I was trying to insert some row from one table to another of different database.
I was using Execute SQL task along with Foreach loop container.
In my execute SQL task I am using this query
SET IDENTITY_INSERT dbo.Table1 ON
INSERT INTO dbo.Table1
SELECT * FROM DB2.dbo.Table2
WHERE TableKey = ?
When executed I get this error:
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
While the same query when executed in Management Studio Its successful.
The properties I set
For Each Loop Editor Settings:
1) Collection: a) Enumerator Set to ForEach ADO Enumerator
b) ADO Object Source Variable: User:bjectVariablename
c) Checked Rows in the first table
2) Variable Mapping: New Int Variable2 and Index = 0 to set it to first colunm.
3) Expression: Left blank
Execute SQL Task Editor:
1) General: a) Timeout : 0
b) CodePage: 1252
c) Result Set: None
d) SQLSourceType: Directinput
e) SQL Statement: SET IDENTITY_INSERT dbo.Table1 ON INSERT INTO dbo.Table1 SELECT * FROM DB2.dbo.Table2 WHERE TableKey = ?
f) BypassPrepare: False
2)Parameter Mapping: Variable Name : New Integer variable2 selected
Direction: Input
DataType: Long
ParameterName: 0
Can somebody help me in this regards.
Reference:
a) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
View 10 Replies
View Related
Jun 21, 2007
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
View 5 Replies
View Related
Nov 8, 2006
Hi,
My Foreach Loop container has 10 different task inside. I want to execute the first task only one time. I have a variable with increases for each repition. How can I put precedence contstraint on the first task so that it should execute only first time and other task has to execute all the time.
Thanks
View 4 Replies
View Related
May 9, 2008
Hi I have created a web service task that sits inside of a foreach loop. The loop is a ado enumerator. This loop is going to loop through about 35,000 records and make 35,000 web service calls.
So far through my testing, I have gotten it to read and write everything correctly, but each call to the web service takes about 1-2 seconds and maxes out the CPU. This would take almost all day for this web service to run at 100% CPU power all day. We want to be able to run this a few times a day or at least once a day.
I have disabled all of the other tasks in the foreach loop so it just calls the web service task and found that it's the web service task that takes up 100% of the CPU and takes 1-2 seconds to run. I have also tried creating a mock web service on the server that just sends back the same mock data, so it's extremely fast, but the Web Service Task still maxes out the CPU and takes 1-2 seconds per call.
I have tried this out on 2 different servers, all with a few gigs of ram they're not using. It seems more like it's a CPU thing, or that the Web Service Task has a high overhead that each time in the loop it takes 1-2 seconds to create the task.
Anyone have any advice?
Thanks,
-Will
View 6 Replies
View Related
Aug 9, 2007
Hi,
I am using an XML task for validating the XML data against the Schema XSD. I have more XML Files with same the schema. I have to used a for loop container which has an XML task for validate XML. The loop container gets the XML File into variables name "XMLFileName" which the loop current file, in turn, used by XML Task for validation.
XML task is configured in the option "Validate". I have provided the XML Data in variable name "XMLFileName" also get the name from the loop container and XSD file content File Connection. XML Task stored the result in the another variable name "OKFormat". FailOnValidationFail property set to false.
It had the error when I run the package, the error msg as below:
Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Data at the root level is invalid. Line 1, position 1.".
Error: 0xC002928F at XML Task, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Task failed: XML Task
After that, I had to change the source type from Variable to File Connection, and had to test fixed to some xml file it had ok for validate the XML file againt XSD.
I don't know this is the wrong setting or bug of SSIS, anyone can guide me through.
View 7 Replies
View Related
Nov 3, 2006
Hi everyone,
do you know any articles on foreach loop container that loops over a dataflow task...pls tell me....
thanks in advance,
View 2 Replies
View Related
Feb 27, 2008
Hi all,
I'm trying to capture the file name and insert to the Database with Loop Container and Execute Sql task...However when I run, I get error with the input parameter.
In my Sql Task, the parameter mapping:
Variable name: user::variable,
direction: input,
Data Type: Varchar,
parameter name: @xVariable,
parameter size: -1
connectiontype: OleDB
sourcetype: direct input
statement : insert into xtable(xcolumn) values(@xVariable)
Anyone have any other suggestion how to capture the filename and input in the database?
thanks
View 4 Replies
View Related
Jan 8, 2008
Hi,
I am trying to use SSIS.Execute Process Task within Foreach loop . The task should unzip a a variable gzip file provided to it from the previous task
to execute the command from cmd C:Program FilesGnuWin32ingzip.exe -d -k c:folderfilename
My input in the Execute Process Task as follow:
The command line: C:Program FilesGnuWin32ingzip.exe
Argument: -d -k c:folder
Variable: User::var_filename
keep getting error
The process exit code was "1" while the expected was "0".
Thanks
View 3 Replies
View Related
Feb 27, 2008
Help with Looping in a SSIS Package
Scenario:
We have a web app that lets our existing clients insert new locations into a table (Clients) in a SQL Server DB. This table has an Identity column as the PK. This table also has another ID field HBID that is the PK for another table (HLocation) in another Database system (Sybase). The HBID field is given a Default value of €˜1€™ during an insert operation in our (SQL Server) table, Clients. The Sybase database uses a Sequence table for inserting a PK into the table. Whenever our clients insert a new record in Clients (SQL Server), I need to generate a new HBID from the Sequence table in (Sybase), update the HBID in the Clients table (SQL Server) and then finally insert the record with the new HBID into table HLocation (Sybase).
I have devised a SSIS package for this as following: Note all variables are scoped at the Package Level.
Execute SQL Task #1 that drives a For Each Loop with the following properties:
General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
SELECT COID, HBID, Name, DateCreated
FROM Client
WHERE HBID = '1'
ResultSet: = Full Resultset
Result Set:
Result Name = 0
VariableName = USER::rsClient Variable Type = Object
ForEachLoop with the follwing properties:
Collection:
Foreach ADO Enumerator
ADO Object Source Variable = USER::rsClient
Enumeration Mode = Rows in first table
Variable Mappings:
Variable: Index:
COID 0
HBID 1
Name 2
DateCreated 3
Inside of the ForEachLoop I have another Execute SQL Task to generate a new HBID from Sybase set up as following.
Execute SQL Task #2
Connection Type: OLE DB
Connection: Sybase
SQL SourceType: Direct Input
SQLStatement:
UPDATE Autoinc SET INC_LAST = (INC_LAST+1) WHERE INC_KEY ='HBID';
SELECT INC_LAST AS NewHBID FROM Autoinc WHERE INC_KEY ='HBID'
ResultSet = SingleRow
Result Set:
Result Name = NewHBID
VariableName = USER::NewHBID, VariableType = Int32
Also Inside the ForEachLoop is a Script Task that has all of my variables as ReadWrite = COID, HBID, Name, DateCreated and NewHBID. I concatenate the values in a string a pass the string into a Message Box to make sure they are looping correctly and they are.For example the results might look like the following:
12698, 1, John Doe Trucking, 10/1/2007, 14550
13974, 1, Joe Smith Trucking, 10/1/2007, 14551
10542, 1, Dave Jones Trucking, 10/1/2007, 14552
Etc.
The values 14550 -14552 are the new HBID being generated in the loop.
The problem is that when I try to Update the HBID in the Client table (SQL Server) with another Execute SQL Task I keep getting the same NewHBID number.
In this case 14550 would be updated for every record instead of the next number in the loop.
I have set up Execute SQL Task #3 as follows:
General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
UPDATE Client
SET HBID = ?
WHERE HBID = '1'(SELECT COID, HBID, Name, DateCreated FROM Client)
ResultSet: = Full ResultSet
Result Set:
Result Name = 0
VariableName = USER::rsNewClient, VariableType = Object
Parameter Mapping:
VariableName USER::NewHBID, Direction = INPUT, DataType = Long
ParameterName = 0
I€™ve tried putting Execute SQL Task #3 inside of the ForEachLoop, connecting it to the output of the ForEachLoop. I€™ve tried setting up a dataflow with a Derived Column using the USER::NewHBID as the Expression.
I still get the same results, 14550 added to every row.
Can any one help or shed some light?
Any and all suggestion will be deeply appreciated!
Thanks !!!
View 1 Replies
View Related
Mar 21, 2007
Hi:
I am a R data miner who is new to SQL and SSIS and would appreciate any help.
I wanted to automate the process of creating and processing decision tree models for every county in the Country. I wanted to use the foreach loop for iterate through all the counties. I wanted the foreach enumerator to be used by the XMLA code that creates the model so it would append it to the name of the model and i would get a different model for every county. I am not sure how to have the XMLA code accept foreach loop enumerator.
Any help would be greatly appreciated and if you could direct me to a previously done example that would greatly benefit me.
Thank you
avneet
View 1 Replies
View Related
Oct 27, 2006
Does anyone know how to change the task name displayed within a ForEach Loop Container (or of the ForEach Loop Container task itself) based on a variable. I am pretty familiar with setting variable values during task execution and using expressions to alter task properties based on variables. I have tried using an expression to alter the value of the Name property of the ForEach Loop Container but the name of the ForEach Loop Container does not change during execution. Since the color of the various tasks change during execution, I would think that the task names could be changed as well.
View 7 Replies
View Related
Jul 19, 2007
hi
I am trying to execute a UDF in an Execute SQL Task within a Foreach loop.
I have an Execute SQL task to get the dataset (Firstname, Lastname, CompanyID) which is going into a full result set called @PendingPar. The Foreach Loop is set to a Foreach ADO Enumerator against @PendingPar and I have created 3 variables at the Foreach Loop scope level called @Firstname (String), @Lastname (String) and @CompanyID (Int32). These are mapped in the Foreach Loop as index 0,1 and 2.
Within the Foreach Loop I have an Execute SQL Task with the sql command
select dbo.udf_GetParTitle (?,?,?)
In the parameter mapping I have Variable Name = User::Firstname Direction = Input Data Type = VARCHAR and Parameter Name = 0, User::Lastname, Input, VARCHAR, 1 and User::CompanyID, Input, VARCHAR,2
Finally the UDF returns a @NewPar value so i have the result set in the execute sql task to Single Row which goes into variable @NewPar. This @newPar will be used later but I cannot get the first bit to work yet.
the error message i get back is
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "select dbo.udf_GetParTitle (?,?,?)
" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task 1
the initial execute sql task where the dataset is selected works fine but it is within the foreach loop that it fails.
any help gratefully recieved
View 6 Replies
View Related
Jun 5, 2006
I have a package that runs fine by itself. But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops. Here are a couple of the error lines:
A buffer failed while allocating 49085616 bytes.
The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
I already played around with the Data Flow task€™s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task. I haven€™t install SP1. Maybe SP1 fixes this issue?
View 3 Replies
View Related
Apr 18, 2008
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
View 8 Replies
View Related
Apr 8, 2008
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
View 1 Replies
View Related
Jul 14, 2007
OK, a new package, with a Foreach container enumerating CSV files in a directory.
I create the container pointing it at the directory and retrieving the fully qualified name, and create a variable (called 'CSVFiles') with a package scope, but no value.
Inside the container is a bulk insert task. The destination db/table is set, and the input flat file connection manager for the CSV files is defined with the connection string set to the variable created above.
As it iterates through the files, the variable is correctly set to the next file in the directory (I put a message box in the stream to display the file name/variable). It resembles 'C: empLocation1.csv'.
But when it gets to the bulk insert, I get this error message:
[Bulk Insert Task] Error: The specified connection "CSVFiles" is either not valid, or points to an invalid object. To continue, specify a valid connection.
What's going on here? Can I not use a bulk insert task in the container? Or some other parameter needs to be set?
SQL Server 9.00.3159
View 4 Replies
View Related
Jan 18, 2007
Hi ALL,
Getting Access Denied To FileName Error When Using the Execute Sql Task (With File Connection) into a Foreach Loop Container.
Please Note :
I have a folder containing .sql files. I have to dynamically loop through the files and send them as a File connection Folder to the Execute Sql Task.
When I run this Package I am getting the follwoing error :
[Execute SQL Task] Error: An error occurred with the following error message: "Access to the path 'C:ProjectsFuzzy Lookup DataFlow ExampleScripts' is denied.".
Also I have logged in to the machine as Administrator and to Sql Server with sa.
Please help.
Thanks.
Regards,
Salil
View 1 Replies
View Related
Apr 4, 2007
How do you sort files from the Foreach loop container?
I love this component, except I can't get it to sort by filename.
Nice to know how to sort by timestamps also.
Dave
View 27 Replies
View Related
Nov 2, 2007
Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.
View 6 Replies
View Related
Apr 22, 2008
Hello
I have a question
How is foreach loop container - foreach ADO enumerator performace in SSIS package compares to use of cursors in stored procedures
Is there any articles comparing them
I understand a lot of factors can affect the performance, however what is expected performance for the foreach ADO enumerator loop for large dataset. What is Microsoft recommendation for that - recommended - not recommended (using large datasets - over million records)
Thank you
Arminr Bell
View 4 Replies
View Related
Apr 11, 2007
Hi,
I have no "Foreach File Enumerator" oprtion in the Enumerator Property of the Foreach Loop Component.
I have this enumerator in the c:Program FilesMicrosoft SQL Server90DTSForEachEnumerators folder.
Also I check it in the GAC - it does not here. I try to execute gacutil.exe -iF ForEachFileEnumerator.dll, but it is failed with "Failure adding assembly to the cache: The module was expected to contain an assembly manifest." Seems it is not managed enumerator.
Please help me.
Also information on how to regeister unmanaged enumerators are welcome!
View 3 Replies
View Related
May 28, 2010
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies
View Related
Jan 12, 2001
I am using the Bulk Insert task object in DTS and I am using a format file as well to import data from a text file. I was able to insert data into my SQL table fine when I did not have a identity column defined in my table; however, I added a identity field and now the bulk insert will not work. Anyone had a similiar problem?
The error msg box that pops up says:
The statement has been terminated.
string or binary data would be truncated.
View 1 Replies
View Related
Aug 23, 2007
I want to check sorted data option for bulk insert task on DTS Package. I want to sort data on 1st column. How can I put order by clause on sorted data option? I put ORDER (SSNo) but I am getting error.
Thanks
ss
View 4 Replies
View Related
Jan 18, 2008
I am doing heavy Bulk insert task to one Database.When i checked the Activity Monitor I saw it was suspended and Wait type:PAGEIOLATCH_EX
So it stopped itself.Do any experts have good idea about what is going on?
Appreciate for help
View 17 Replies
View Related
Jun 5, 2007
Dear All,
For ex:
In SQL Server 2005 when we create table with 3 columns which has allow nulls option is disabled. Then we try to insert record with blank to any one of the column SQL server throws error, coz column name is NOT Null without inserting data it won't saves the record.
When we try to import a csv data file has 3 columns & has null data. If we use BULK INSERT TASK option, it easily inserts the data, without throwing error.
My question is how can we restrict null data without inserting it into the table using BULK INSERT TASK using SQL Server 2005.
Please suggest me to solve this issue.
Thanks in advance
Karna
View 5 Replies
View Related
Jan 25, 2007
Hi,
I am using the Bulk Insert Task to bulk copy data from a flat file to a SQL Server 2005 table. The flat file contains pipe ( | ) delimited columns and several thousand records which are {CR}{LF} delimited. The file also contains a header record and trailer record, which contain several words but also contains a ( | ) symbol among those words.
E.g.:
HEDR | yadi yadi yada500 | Data Data | More Data | 600460 | Datum | More More | 705550 | Data | Data | 603FOOTR | yadi yadi yada
I need to ignore the header and trailer records and just pickup the proper records. But even though I manually set the First Row property to 2 and the Last Row property to 4, It does not pickup the first true record i.e. the record which begins with 500, and if I set the First Row to 1, it throws me an error citing 'truncation error' or similar. I think it is taking the first record (i.e. header row along with the first row as one, and since there are now more pipes ( | ) the error is thrown)
I've tried setting different values for these properties but to no avail... Any help will be deeply appreciated...
Thanks
Gogula
View 3 Replies
View Related
Sep 19, 2006
i need to do a bulk insert for every csv file from a particular folder & i wish to do this programatically.
i hav tried a "foreach" loop & using variables for the filenames but it doesnot seem to be working & i just cant figure out why !!
View 3 Replies
View Related
Aug 28, 2006
hi,
i have a bulk insert task and i want to transfer to the source connection each time a different
text file name. how do i transfer to the connection source field a parameter with a new name
each time. the name will b excepted as a parameter to the package. can i do it in a bulk insert
task?
thx,
Tomer
View 4 Replies
View Related
Mar 16, 2004
Really Don't understand this and have run out of hair to pull out.
DTS package whihc has a Bulk Insert Task. The task is populated with its source filename dynamically. It has a format file which is stored locally to the box its running on.
The output file is from a unix box pipe delimited. Great dandy. All works a treat as long as I treat everything as either an INT or CHAR. But I have a couple of columns that are DATES in the format 13/02/2004.
I want these inserted into smalldatetime columns. Will settle for datetime columns though.
I get an error on every row Bulk Insert data conversion error (type mismatch) for Row X Column 11 (PostingDate) which happens to be the first date column.
Tried loading it as CHAR, SQLDATETIME and SQLDATETIM4 in the format file all to no avail. TRied it with columns in the dest table as DATETIME and SMALLDATETIME
Anyone have any ideas?
View 9 Replies
View Related
