ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

You may have to make sure there are selected under the SSIS Control flow items. Right Click on Control Flow task tab - select choose item... - select SSIS Control flow tab - you should see the foreachfile and foreachitem controls - make sure they are selected.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

|||It sounds like your installation has been corrupted. You have the For and ForEach Shapes in the toolbox, but when you drag the ForEach shape over you are missing 2 of the values in the drop-down for the type of enumerator. Is this correct? If so you may need to do a reinstall.|||

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

You may have to make sure there are selected under the SSIS Control flow items. Right Click on Control Flow task tab - select choose item... - select SSIS Control flow tab - you should see the foreachfile and foreachitem controls - make sure they are selected.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

|||It sounds like your installation has been corrupted. You have the For and ForEach Shapes in the toolbox, but when you drag the ForEach shape over you are missing 2 of the values in the drop-down for the type of enumerator. Is this correct? If so you may need to do a reinstall.|||

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

You may have to make sure there are selected under the SSIS Control flow items. Right Click on Control Flow task tab - select choose item... - select SSIS Control flow tab - you should see the foreachfile and foreachitem controls - make sure they are selected.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

|||It sounds like your installation has been corrupted. You have the For and ForEach Shapes in the toolbox, but when you drag the ForEach shape over you are missing 2 of the values in the drop-down for the type of enumerator. Is this correct? If so you may need to do a reinstall.|||

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

foreach loop?

I need to execute about dozen packages from another package... how do I dynamically pass the dozen package names to the package and execute using foreach loop...?

idea is to store the names of packages in a text file and set the file connection property reading each package names from the text file... in this way I can just configure/edit the text file from time to time, the packages and the units that I want to execute...

Someone please provide me steps to make it work.

Thanks in adv.

You need to load the contents of the file into an ADO Recordset using a data-flow. You can then shred that recordset using the ForEach loop. This example demonstrates the same - the only differrence being that the ADO recordset is populated using an Execute SQL Task rather than a data-flow. The shredding is exactly the same though: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

-Jamie

|||

Thanks Jamie.... it worked wonderfully!

|||I need a sql Query to loop through a column in one table reading the ID of tenants, the result being a list of the Tenants names from another table with the same tenantID's. The captured data needs to be filled into textboxes on a form.sql

foreach loop?

I need to execute about dozen packages from another package... how do I dynamically pass the dozen package names to the package and execute using foreach loop...?

idea is to store the names of packages in a text file and set the file connection property reading each package names from the text file... in this way I can just configure/edit the text file from time to time, the packages and the units that I want to execute...

Someone please provide me steps to make it work.

Thanks in adv.

You need to load the contents of the file into an ADO Recordset using a data-flow. You can then shred that recordset using the ForEach loop. This example demonstrates the same - the only differrence being that the ADO recordset is populated using an Execute SQL Task rather than a data-flow. The shredding is exactly the same though: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

-Jamie

|||

Thanks Jamie.... it worked wonderfully!

|||I need a sql Query to loop through a column in one table reading the ID of tenants, the result being a list of the Tenants names from another table with the same tenantID's. The captured data needs to be filled into textboxes on a form.

Foreach Loop, Data Flow task buffer failed

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?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach Loop, Data Flow task buffer failed

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?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach loop over Excel files seems 'fragile'

All,

I have a package that loops over ~60 Excel files in a directory. Each
file has three named ranges in it, which I import into different
tables. Sometimes the package runs without a hitch, sometimes it
chokes. But it is intermittent.

If I pull the control flow components out of the foreach loop and
point the Excel connection manager to the specific Excel file that has
caused the package to choke, I get a message in the dataflow component
pointing to the named range that "the metadata of the following output
columns does not match the metadata of the external columns......Do
you want to replace the metadata of the output columns with the
metadata of the external columns?" When I choose 'yes', then the
file will be loaded. then I can put the control flow components back
into the foreach loop and the file will run again, successfully, along
with some more, until it chokes again....

So, first of all, does anyone have any insight into this? Sometimes,
somedays, these files will load with no problems. These exact files;
I am having to reload constantly... Other times, like today, it is a
battle.

Otherwise, is there a way to get Integration Svcs to handle the
metadata issue on the fly?

Any ideas, resources, references, war stories, or good clean jokes
would be appreciated,
Kathryn

Metadata cannot change... Do you have changing metadata in your Excel documents, or does SSIS just think it is changing?|||

Phil,

Thanks for the quick reply. It seems that SSIS thinks the metadata is changing..

As far as I can tell, the problem is caused when a field in the file does/does not have a hyphen in it. For example, some files give us EIN with a hyphen and some don't. The package will chug along until it gets an EIN with a hyphen, then it will choke. I will pull the control flow components out of the foreach, point the excel source at the file that's causing it to choke, then i will answer yes to the metadata warning. Then I'll put the control flow components back into the foreach and it will chug along until it gets to a file WITH a hyphen in the EIN, when it will choke again....

All fields are defined to be strings. I even put a Data Conversion component after the Excel Source component to strip out hyphens, but the data flow doesn't get to the Data Conversion; it chokes on the Excel Source.

Kathryn

|||

Hey Kathryn,

Try this... I don't know if it'll work or if you've already tried this, but try to process the erroneous file first (if possible) in the loop. I don't know if you can control that or not. Here's what I'm thinking. I think that SSIS looks at the first file, sees that FieldA1 is a numeric, and sets the metadata to numeric for that field. When you encounter a text value for that same field in a subsequent file, it bombs. So I'm wondering if you can process a file first that contains the text value of that field, for example. Then it'll think that field is a text field and process it the same for the rest? It's just a thought!

Rebecca

|||Maybe setting IMEX=1 in the excel connection string is the answer here as well.|||

Phil,

Thanks for the suggestion. Unfortunately, it didn't work, though it seems that that should be the answer....

Kathryn

|||

I'm very surprised that IMEX=1 did not work, since forcing everything to be loaded as a string should avoid the issue with the mixed data types that you otherwise have in your EIN column (numeric values when there's no dash, string values when there is one).

The only potential issue that comes to mind is the difference between string and memo fields, for which there must be at least 1 row with a memo value in the rows sampled by the driver for the driver to recognize that column as a memo column.

Let's remember that Excel has no column metadata. The driver can only guess.

-Doug

sql

Foreach Loop Issue

Here is what I am attempting to get accomplished. I have an SSIS package that contains a Foreach loop container. This container executes a number of SQL tasks in order: SQL Task 1, SQL Task 2, SQL Task 3.

if the SQL task 1 succeeds it should flow on to SQL Task 2 and 3. This works fine when the SQL tasks do not fail...

In the event of any SQL Task failing control should flow to a send mail task to alert about the failure. Next the Foreach loop container should go to the next enumeration in the Foreach loop container and start the next new SQL task 1. So far I have been able to get the control to flow to the send mail task when a SQL Task fails. What does not work is when one SQL Task fails the entire Foreach loop fails and does not move to the next enumeration. It should only fail the package and move on.

Any help would be appreciated....

Please check the FailPackageOnFailure and FailParentOnFailure properties of ForeachLoop Container as well as Execute SQL Task Object. if any of them is defined as true then set it to false.

If this will not help you let me know.

|||

I have checked these properties and i have both of them set to 'False'... Still Fails...

Any other suggestons?

|||I suppose you could ForceExectionResult = Success|||

Hi Steve,

Here is the Solution:

1. For Foreach Loop container set "ForceExecutionResult" to "Success" so that this container never failes on execution.
2. For precedence constraint of all your tasks in Foreach loop container set the "Value" property as "Completion" so that next SQL task get executed only on COMPLETION of previous SQL task and not SUCCESS of previous one.
3. Set the "FailParentOnFailure", "FailPackageOnFailure" property to "False" for all SQL Task in container. Set "ForceExecutionResult" property to "None" for all SQL Task in container.
4. I am sure you are using "Failure" precedence constraint to send mail task from SQL Tasks.

I created a test package to try this scenario and it works :)

Thanks
Mohit

ForEach Loop container issue!!!

Hi everyone,

I am having hard time with foreach loop container. The for each loop container in my package goes over all the rows in a ADO enumerator recordset variable and shows row values one by one in message box. The problem is that it just keeps printing the first row infinitely. Could anyone tell me what could be wrong?

Thanks in Advance,

Care to share the code on how you build the message box?|||

Praveen Dayanithi wrote:

Hi everyone,

I am having hard time with foreach loop container. The for each loop container in my package goes over all the rows in a ADO enumerator recordset variable and shows row values one by one in message box. The problem is that it just keeps printing the first row infinitely. Could anyone tell me what could be wrong?

Thanks in Advance,

Jamie Thomson has a sample package here:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

|||

Check this feedback:

ForEach enumeration of ADO recordset can cause infinite loop when using checkpoints

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125915

Foreach Loop Container causes package to crash

We have a problem with a SSIS package containing a Foreach Loop Container that causes the package to fail unpredictably.

We are using a Foreach Loop Container to process records in a source table one by one. We do this by executing a SQL statement on the source table, putting the resultset in a package variable and using that variable as an ADO object source variable in a Foreach Loop Container. In that container, we do four things:

1) copy the record we want to process into a temporary table,
2) run a dataflow task on that temporary table to actually process the record,
3) truncate the temporary table filled in step 1 and
4) delete the processed record from the source table.

This part of the package validates and runs fine, but every now and then the package fails somewhere in the Foreach Loop Container without any useful notification. We cannot tell where exactly the package fails: it differs. It's often in the dataflow task, but not always. If we clean up the step in which the package fails and rerun it (such that the last row in the source table is processed again), it continues without a problem. Sometimes it stops in the middle of processing a specific record. If we leave the record in the source and process that record again, it processes fine without failing on that record again. So it's not one of the source records causing the problem. One time it will take a couple of hundred iterations before the failure occurs, the next time it might take less than a hundred.

Does anybody have any clue on what might cause this problem or what we can do to further investigate this?

Thanks in advance, Hans Geurtsen

Does "without any useful notification" mean that no errors are shown or that you don't find the error(s) useful. If there are errors then can you please provide them. Without the errors all I can do is hazard a guess. Perhaps you are encountering locking issues or perhaps there are memory problems due to fragmentation.

Thanks,

Matt

Foreach Loop and Package Configuration

I am trying to build a package that moves data from one server to another. My plan is to make the package dynamic in that the source and destination connection and sql statements are strored in the package configuration.

Is it possible to have a foreach container loop through each configuration?

Thanks,
Russ Jester

And do what? Why do you want to loop through a configuration (I presume you mean a configuration file)?

-Jamie|||I wanted to set configuration items and store them in a configuration database. Idaally I would like to loop through each configuration item to load data from source tables to my target.

I saw this as a way to build a package that would load data based on the source table, source sql, and target table pulled from the configuration. Instead of having to create a package for each table.

Am I not understanding the purpose of the package configuration?

ForEach Loop - Testing for when Enumerator is Empty

I have a SSIS package this set to run at a specific time each day. If there are no files for the ForEach tool to work upon...while it doesn't 'fail'...I would like to test for the condition that the enumerator was empty...so that I could send an email message reminding someone to followup and investigate.

What would be the best way to test for that condition?

Cordell,

You could add a counter variable to the package, use a Script Task within the ForEach loop to increment the variable, and then use an expression on a precendence constraint following the loop to decide to mail based on the count variable still being zero.

Here are some helpful links to get you started:
About variables - http://msdn2.microsoft.com/en-us/library/ms141085.aspx
Using variables in Script Tasks - http://msdn2.microsoft.com/en-us/library/ms135941.aspx
Precendence Constraint - http://msdn2.microsoft.com/en-us/library/ms141261.aspx

Cheers,
Patrik

|||

thank you Patrik for researching a solution for my need. I thought this is what I would have to end up doing, but wanted to make sure I was not missing something obvious.

It would be nice in the next major update of SSIS that this condition would be provided as an attribute/event to test for in the ForEach Loop tool.

...cordell...

p.s. Is there a place at MSDN to enter feature requests such as this?

|||

Cordell,

Feedback can be submitted to http://connect.microsoft.com.

Glad I could help,
Patrik

ForEach file enumeration with bulk insert problem

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:\temp\Location1.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

You have to use a file connection instead of the variable.

HTH.

|||

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

|||

Kevin6 wrote:

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

You'd have to put the variable in the expression editor for the property "ConnectionString." Right click on the connection manager object and select properties. Scroll down to find "Expressions." Click the ellipsis and select ConnectionString. There is where you put the variable name.

Re: Execute SQL Task
Make sure that the variable is of package-level scope and that the Execute SQL Task can see it.
Then build a SQL statement like this:
insert into auditTable values (1,"Testing", ?)

Then click on the parameter mapping tab, click Add, and select the variable in the variable name column. Then use zero (0) for the parameter name. Change the data type to "VARCHAR". If you have another parameter, do the same, but use a one (1) in the parameter name box.

Forcing a package to run as 32 bit on a x64 machine using SQL Server Agent?

I have a need to force a package to run using the 32-bit runtime from the SQL Server Agent. The machine is a x64 unit. I'm having to use an ODBC driver to extract data from our ERP package that will only run in 32 bit. Any help would be appreciated.

Hi.

Here is another suggestion: run distributed queries through SqlExpress/32. Here is a sample for MS Access:

http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html

Hope this helps

|||

In Agent, use an Operating System (CmdExec) step and use the copy of dtexec.exe in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

hth

Donald Farmer

|||Thanks, Donald. That worked like a charm.sql

Forcing 32 bit SSIS

I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.

When I run it in 32 bit mode using the GUI, it runs all the way through.

Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?

Thanks
Larry C

I haven't used Itanium, but I suspect the following method will hold true-

On a 64-bit version of SQL Server 2005, if you wish to schedule a package to execute a package under 32-bit mode, you will have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type will always use the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTEXC should be used. As above the 32-bit version can be found in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.

x64
(http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html)

|||Thanks so much, that will get us by great until we can resolve the issue we're having with the 64 bit version.

Forcing 32 bit SSIS

I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.

When I run it in 32 bit mode using the GUI, it runs all the way through.

Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?

Thanks
Larry C

I haven't used Itanium, but I suspect the following method will hold true-

On a 64-bit version of SQL Server 2005, if you wish to schedule a package to execute a package under 32-bit mode, you will have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type will always use the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTEXC should be used. As above the 32-bit version can be found in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.

x64
(http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html)

|||Thanks so much, that will get us by great until we can resolve the issue we're having with the 64 bit version.|||

Larry does it worked for you in Itanium 2,please share your experience.

Thanks

|||It worked for us in 32bit mode ok. We had lots of little things that didn't work or worked differently on the Itanium and eventually decided to move all jobs to a 32bit box. We haven't had any issues since.

Force IS to use column headings

Hi,
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.

The problem:
IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.

Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?

Thanks guys...

The connection manager defines the ordering in the text file so if IS is putting your data into the wrong columns in the database it is because the connection manager is defined incorrectly. If your files vary their order of columns then you would need different connection managers (and therefore different sources) for each ordering.

Matt

|||Thanks for the reply but the problem I'm facing is the text file may not have some of the columns or the columns will be in different order etc. I don't know what the file contains at the time of loading.

Is it possible for me to get IS to load what ever columns are in the text file and just put null (in the database) for the once we are missing?

|||

If you really have no idea what is coming in until it's loaded, then my suggestion would be to load the text file, including the first row with the names, into a SQL table with columns called "col1", "col2", "col3" etc. up to the max you will have. That gets you over the problem of loading the table using a single data flow task.

Then the problem is one of how to split the data into the relevant columns in your "proper" destination table. ;-D

I'm still gettng my head round the new tools in SSIS, so personally I wouldn't know how to do it (maybe conditional split?). What I would do would be to write a T-SQL stored procedure to parse the first row and construct an SQL string to select the columns from the table.

The example below gives you the idea.

Hope this helps,

Rich

Code to follow --

CREATE TABLE [dbo].[tbl_RAW](

[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_People](

[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Phone] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Sex] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_ValueList](

[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

TRUNCATE TABLE tbl_RAW;

TRUNCATE TABLE tbl_People;

TRUNCATE TABLE tbl_ValueList;

-- Populate RAW

INSERT INTO dbo.tbl_RAW

Values ('sex','Name','Phone');

INSERT INTO dbo.tbl_RAW

Values ('Male','Eric','1234');

INSERT INTO dbo.tbl_RAW

Values ('Male','Tim','00000');

INSERT INTO dbo.tbl_RAW

Values ('Female','Simone','9876');

INSERT INTO tbl_ValueList

SELECT top 1 *

FROM dbo.tbl_RAW

DECLARE @.ValueList varchar(50)

DECLARE @.strSQL varchar(100)

DECLARE @.col1value varchar(50)

SELECT @.ValueList = '(' + col1 +','+ col2 +','+ col3 +')' FROM tbl_ValueList

SELECT @.Col1Value = col1 FROM tbl_ValueList

SET @.strSQL = 'INSERT INTO dbo.tbl_People ' + @.ValueList + 'SELECT * FROM dbo.tbl_RAW WHERE col1 <> '''+ @.col1Value +''''

print @.strSQL

EXECUTE (@.strSQL)

SELECT * FROM dbo.tbl_People

-- End of Code--

|||

I'm having a similar problem.

I'm using CSVDE.exe to do a bulk export of Active Directory users. The problem is that the column order that CSVDE outputs seems to be non-deterministic.

If I dump the file, go into the connection and do a "Reset Columns", everything works fine. However, I would like to do the dump as part of my Control Flow, and I can't find a way to force a "Reset Columns" before the processing begins.

I know what columns I'm getting, just not the order they'll come in. I also know that the first row will have the column names in it.

Force IS to use column headings

Hi,
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.

The problem:
IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.

Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?

Thanks guys...

The connection manager defines the ordering in the text file so if IS is putting your data into the wrong columns in the database it is because the connection manager is defined incorrectly. If your files vary their order of columns then you would need different connection managers (and therefore different sources) for each ordering.

Matt

|||Thanks for the reply but the problem I'm facing is the text file may not have some of the columns or the columns will be in different order etc. I don't know what the file contains at the time of loading.

Is it possible for me to get IS to load what ever columns are in the text file and just put null (in the database) for the once we are missing?

|||

If you really have no idea what is coming in until it's loaded, then my suggestion would be to load the text file, including the first row with the names, into a SQL table with columns called "col1", "col2", "col3" etc. up to the max you will have. That gets you over the problem of loading the table using a single data flow task.

Then the problem is one of how to split the data into the relevant columns in your "proper" destination table. ;-D

I'm still gettng my head round the new tools in SSIS, so personally I wouldn't know how to do it (maybe conditional split?). What I would do would be to write a T-SQL stored procedure to parse the first row and construct an SQL string to select the columns from the table.

The example below gives you the idea.

Hope this helps,

Rich

Code to follow --

CREATE TABLE [dbo].[tbl_RAW](

[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_People](

[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Phone] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Sex] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_ValueList](

[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

TRUNCATE TABLE tbl_RAW;

TRUNCATE TABLE tbl_People;

TRUNCATE TABLE tbl_ValueList;

-- Populate RAW

INSERT INTO dbo.tbl_RAW

Values ('sex','Name','Phone');

INSERT INTO dbo.tbl_RAW

Values ('Male','Eric','1234');

INSERT INTO dbo.tbl_RAW

Values ('Male','Tim','00000');

INSERT INTO dbo.tbl_RAW

Values ('Female','Simone','9876');

INSERT INTO tbl_ValueList

SELECT top 1 *

FROM dbo.tbl_RAW

DECLARE @.ValueList varchar(50)

DECLARE @.strSQL varchar(100)

DECLARE @.col1value varchar(50)

SELECT @.ValueList = '(' + col1 +','+ col2 +','+ col3 +')' FROM tbl_ValueList

SELECT @.Col1Value = col1 FROM tbl_ValueList

SET @.strSQL = 'INSERT INTO dbo.tbl_People ' + @.ValueList + 'SELECT * FROM dbo.tbl_RAW WHERE col1 <> '''+ @.col1Value +''''

print @.strSQL

EXECUTE (@.strSQL)

SELECT * FROM dbo.tbl_People

-- End of Code--

|||

I'm having a similar problem.

I'm using CSVDE.exe to do a bulk export of Active Directory users. The problem is that the column order that CSVDE outputs seems to be non-deterministic.

If I dump the file, go into the connection and do a "Reset Columns", everything works fine. However, I would like to do the dump as part of my Control Flow, and I can't find a way to force a "Reset Columns" before the processing begins.

I know what columns I'm getting, just not the order they'll come in. I also know that the first row will have the column names in it.

force exit

Hi,

I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?

"to force exit" is equivalent with "no execution", so

try to use "precedence constraints" (on that green arrow): if "a_condition" is true execute ftp task else is not run anythink;

to get the information that the file exists i think it is a possibility using WMI in a ActiveX script task and testing a variable (to build "a_condition")- it is an ideea|||I have a success contraint to continue if it's there and a on failure constraint to quit, which it does but it keeps sending my on error email but there is no error? I even have the failpackageonfailure property set to False and it still keeps giving the error.|||

I got help by going to this blog, it worked perfect.

http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

|||

Exactly what I thaught: the blogger build the condition and use the precedence constraints !