Help --FOr Each Loop Container (For each file Enumerator) SSIS package

Can anyone help me with FOr Each Loop Container (For each file
Enumerator) control flow SSIS package
If I use FOr Each Loop Container (For each file Enumerator), it will
select all the files in that folder. What if I want to select just 100
files (assuming 500 files in the folder)
How do i do this?
Thanks
Hi
I am not a SSIS expert, but a for loop can be used with a counter
http://www.sqlis.com/default.aspx?310 but I am not sure how this could be
done with a foreach loop. I guess you could have an activeX task that checks
a variable and returns failure when the counter is exceeded or increments it
and returns success when not.
John
"Vishal" wrote:

> Can anyone help me with FOr Each Loop Container (For each file
> Enumerator) control flow SSIS package
> If I use FOr Each Loop Container (For each file Enumerator), it will
> select all the files in that folder. What if I want to select just 100
> files (assuming 500 files in the folder)
> How do i do this?
> Thanks
>

Help --FOr Each Loop Container (For each file Enumerator) SSIS package

Can anyone help me with FOr Each Loop Container (For each file
Enumerator) control flow SSIS package
If I use FOr Each Loop Container (For each file Enumerator), it will
select all the files in that folder. What if I want to select just 100
files (assuming 500 files in the folder)
How do i do this'
ThanksHi
I am not a SSIS expert, but a for loop can be used with a counter
http://www.sqlis.com/default.aspx?310 but I am not sure how this could be
done with a foreach loop. I guess you could have an activeX task that checks
a variable and returns failure when the counter is exceeded or increments it
and returns success when not.
John
"Vishal" wrote:
> Can anyone help me with FOr Each Loop Container (For each file
> Enumerator) control flow SSIS package
> If I use FOr Each Loop Container (For each file Enumerator), it will
> select all the files in that folder. What if I want to select just 100
> files (assuming 500 files in the folder)
> How do i do this'
> Thanks
>

help converting filetime bigint to SQL datetime

Hello,
I am using a DTS package to putt information from a Windows 2003 Active
Directory into SQL 2000.
The DTS package connects to AD, which is set up as a linked server, and
reads several attributes of user accounts, including the accountExpires and
lastLogonTimestamp columns.
The accountExpires and lastLogonTimestamp fields store dates and times. I
found an excellent technet article by the Scripting Guys that explains that
this value is stored in AD as a 64-bit integer, and that it represents the
number of 100-nanosecond units since January 1, 1601.
Unfortunately, SQL Server doesn't store dates that way. The datetime type in
SQL server is the number of three-hundredths of a second since 01/01/1753.
I thought about writing a conversion function, but I realized two things: 1)
someone must have encountered this before, and 2) it's not as easy as it
might seem at first glance because of leap years.
Moreover, I can't do anything about it in the DTS package with a custom
ActiveX Transformation because (I think) the ActiveX implementation in DTS
only handles 32-bit integers.
Can anyone help me with this? I really need to store those two columns in
SQL Server.
Thank you!
- Michael GibbonsHi Michael
You could try subtracting the difference (in 100 ns units) between the two
base dates, convert the remainder to nano-seconds and then adding to the SQL
Server base date.
John
"Michael" wrote:

> Hello,
> I am using a DTS package to putt information from a Windows 2003 Active
> Directory into SQL 2000.
> The DTS package connects to AD, which is set up as a linked server, and
> reads several attributes of user accounts, including the accountExpires an
d
> lastLogonTimestamp columns.
> The accountExpires and lastLogonTimestamp fields store dates and times. I
> found an excellent technet article by the Scripting Guys that explains tha
t
> this value is stored in AD as a 64-bit integer, and that it represents the
> number of 100-nanosecond units since January 1, 1601.
> Unfortunately, SQL Server doesn't store dates that way. The datetime type
in
> SQL server is the number of three-hundredths of a second since 01/01/1753.
> I thought about writing a conversion function, but I realized two things:
1)
> someone must have encountered this before, and 2) it's not as easy as it
> might seem at first glance because of leap years.
> Moreover, I can't do anything about it in the DTS package with a custom
> ActiveX Transformation because (I think) the ActiveX implementation in DTS
> only handles 32-bit integers.
> Can anyone help me with this? I really need to store those two columns in
> SQL Server.
> Thank you!
> - Michael Gibbons

Help calling DTS package.

I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting"
Error Number:
-2147467259 {Integer}

Error Description:
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}

This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.

Any ideas??

ThanksAfter a few modifications I'm getting a different error now. But it still is obviously a permissions problem. Everything I've read says set Impersonation equal to true. Unfortunately it already is. Any suggestions from you SQL Server gurus?

"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

Thankssql

HELP - urgent situation

Hey all

I've got a DTS package with a script that does this:

Set xlApp = CreateObject("Excel.Application")

This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??

Thanks! I gotta get this solved within 5 hrs :(I am by no means an expert on this particular situation, but I would think that your 'best' (meaning most likely to work the first time) solution would be to install MS Office (just the Excel portion of it) on the server.

Regards,

hmscott|||Our in-house IT guy won't install Excel/Office on the server that our SQL Server is on.. something about opening up for potential problems and/or security issues.

It seems to me that if you CreateObject("Excel.Application"), that there's some kind of ActiveX .dll that could be registered on the server to allow the creation of these Excel COM objects. Anyone know which files they are??|||Where is the Package called from?

If client side app I would suggest installing Excel would work,

If a VB app adding the Excel distributables in the compile should work.

Create a reference to the Excel Object in your VB app.

mine is at
C:\Program Files\Microsoft Office 2000\Office\EXCEL9.OLB

When installed the required registry entries should follow.

Good Luck|||It's an ActiveX script within a DTS package on the server. I'd LIKE to have Office installed, but IT won't allow it.

Is there some way I could place the EXCEL9.OLB file on the server, and reference it somehow in my VBScript??|||All DTS Packages are run on the client unless scheduled by SQL Server regardless of where the code resides.

Your users will need Excel on their PC to use the output anyway, as well as DTS dlls (read SQL Server Client).

Is XML an option? or a structured text file which they can import into Excel. If so you could use the fileScripting object in Active script.|||Well.. actually this is a web page executing the DTS package.. so all the proper DTS dlls are installed on the web server now. And the DTS package has an ActiveX script that tries to create an instance of Excel.Application..but can't do so because Office is not installed on the web server (and will never be).

Nope.. XML is not an option. They gave us an Excel workbook they want populated.. there's no room to budge on this, unfortunately.|||If all you are doing is a data migration into a template try to map to the Excel spreadheet and se a dtsDataPump|||What if you pumped the data to a Comma Separated Value file (.csv), and let Excel convert it from wherever the client happens to open it from?|||OK here's the solution we went with.. MS Office Web Components.

Problem is, I can't find any thorough documentation on it. I want to know if it's possible to open an Excel file with it.

Set objOWC = CreateObject("OWC10.Spreadsheet")

That's how it's created. I have a few examples that tell you how to create a new Excel file from scratch and save it. But not how to open an existing one, modify it, then save to a new file.

Does anyone know, or can point me to some documentation? Thanks!

Help - Tasks that have circular dependencies

Hello,

I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).

The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.

However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.

I hope that I have explained this well enough.

I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution?

I am getting desperate, so any suggestions will be welcome

Many thanks

The Foreach Loop Container may be of use. have you evaluated that?

Regards

-Jamie

|||

Hello Jamie,

Thanks for the reply.

Yes, I have, but as I have to pass it a recordset at the start of the for each.. it doesn't seem to fit the bill. I have to pick up these tables in an order only defined at run time by the sub package.

|||The Foreach container is exactly what you need to use. You will need to store your recordset in a user variable using an ExecuteSQL task and then use the Foreach container with a Foreach ADO enumerator. More direction is available on BOL but that should be enough to get you started.|||

Oh dear,

I am not explaining this very well, I;m sorry.

On the first pass I will get a table name from ny ontrol table which beeds to be populated. Once this is populated, I will mark the parent tables next to be processed in my control table.

So on the second iteration, I need to retrieve a new recordset to pass to the For Each loop. And so on.

Sorry if I am being obtuse, but I can't see how I can make this work.

Help - DTS package crashing with broken connection?

All,
Windows XP SP2 + hotfixes
SQL Server 2000 SP3
Has anybody seen this one? And is there a fix/workaround for it?
I have a DTS package that loads data in from CSV formatted flat files. The
package executes Ok for two small tables however it crashes about 10,000
records into a transformation task which is loading a flat file of 230,000+
records. Here's the error:
Step 'DTSStep_DTSDataPumpTask_3' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. (Microsoft OLE DB Provider for SQL Server (80004005): The
statement has been terminated.) (Microsoft OLE DB Provider for SQL Server
(80004005): Cannot insert duplicate key row in object 'Staff' with unique
index 'IX_StaffUserName'.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
Step Execution Started: 9/9/2005 3:41:52 PM
Step Execution Completed: 9/9/2005 3:42:07 PM
Total Step Execution Time: 15.391 seconds
Progress count in Step: 42000
The task runs a VBScript and has 1 Lookup - a check to see if a row exists
before inserting. The Lookup is using the same connection as the Load {whic
h
I read is not good} but it must do so because the existence check is against
the table being loaded and loaded rows are not visible to other
connections...
Any ideas how I can a) circumvent this issue or b) allow load processing to
'quietly' fail on duplicate inserts but run to completion anyway?Look like you got a duplicate key problem when inserting data into Staff
table, violation against index IX_StaffUserName. Clean out your data before
import doing the import again or import those data into a staging table with
no restriction and then clean out your data before import to the main table.
Q
"Richard" wrote:

> All,
> Windows XP SP2 + hotfixes
> SQL Server 2000 SP3
> Has anybody seen this one? And is there a fix/workaround for it?
> I have a DTS package that loads data in from CSV formatted flat files. Th
e
> package executes Ok for two small tables however it crashes about 10,000
> records into a transformation task which is loading a flat file of 230,000
+
> records. Here's the error:
> Step 'DTSStep_DTSDataPumpTask_3' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
> Step Error Description:The number of failing rows exceeds the maximum
> specified. (Microsoft OLE DB Provider for SQL Server (80004005): The
> statement has been terminated.) (Microsoft OLE DB Provider for SQL Server
> (80004005): Cannot insert duplicate key row in object 'Staff' with unique
> index 'IX_StaffUserName'.)
> Step Error code: 8004206A
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:0
> Step Execution Started: 9/9/2005 3:41:52 PM
> Step Execution Completed: 9/9/2005 3:42:07 PM
> Total Step Execution Time: 15.391 seconds
> Progress count in Step: 42000
> The task runs a VBScript and has 1 Lookup - a check to see if a row exists
> before inserting. The Lookup is using the same connection as the Load {wh
ich
> I read is not good} but it must do so because the existence check is again
st
> the table being loaded and loaded rows are not visible to other
> connections...
> Any ideas how I can a) circumvent this issue or b) allow load processing t
o
> 'quietly' fail on duplicate inserts but run to completion anyway?
>
>|||I think this response from Louis was meanbt for you.
Chekc this KB article for the explanation:
http://support.microsoft.com/defaul...kb;en-us;329329
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:5A302543-FB5F-484B-9E7D-06943BEACC04@.microsoft.com...
> All,
> Windows XP SP2 + hotfixes
> SQL Server 2000 SP3
> Has anybody seen this one? And is there a fix/workaround for it?
> I have a DTS package that loads data in from CSV formatted flat files.
The
> package executes Ok for two small tables however it crashes about 10,000
> records into a transformation task which is loading a flat file of
230,000+
> records. Here's the error:
> Step 'DTSStep_DTSDataPumpTask_3' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
> Step Error Description:The number of failing rows exceeds the maximum
> specified. (Microsoft OLE DB Provider for SQL Server (80004005): The
> statement has been terminated.) (Microsoft OLE DB Provider for SQL Server
> (80004005): Cannot insert duplicate key row in object 'Staff' with unique
> index 'IX_StaffUserName'.)
> Step Error code: 8004206A
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:0
> Step Execution Started: 9/9/2005 3:41:52 PM
> Step Execution Completed: 9/9/2005 3:42:07 PM
> Total Step Execution Time: 15.391 seconds
> Progress count in Step: 42000
> The task runs a VBScript and has 1 Lookup - a check to see if a row exists
> before inserting. The Lookup is using the same connection as the Load
{which
> I read is not good} but it must do so because the existence check is
against
> the table being loaded and loaded rows are not visible to other
> connections...
> Any ideas how I can a) circumvent this issue or b) allow load processing
to
> 'quietly' fail on duplicate inserts but run to completion anyway?
>
>

HELP - ActiveX stuck in loop, cannot kill!

Someone please help--

I've been working on an ActiveX script in a DTS package. I executed the script and it's stuck in a while..wend loop. If I kill it I will lose all the code I wrote (pretty much a lot) since last saving the entire package.

Is it possible either:

a) Kill the activex step without killing the entire DTS designer session.
b) Locate the ActiveX code which must be in a temp table somewhere.

Please help I don't want to recode the entire object.

Thanks!If you kill the spid when eunning a query in qa it doesn't shut qa...

Now don't take my word for it, but maybe if you kill the spid, the designer session will still be open...

BUT WHY would you run something before saving and WHY would you have a loop of any kind?