Bulk load architecture

Question

Bulk load architecture

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 1

Hi guys, I was able to create SSIS package to load multiple files in a folder. Now could you please direct me on how to put dynamic columns value population ? I have to populate version and date of load. For example, if there is first load, version should be 1 with date as today's date. If I again try to load, then version should be 2 and date again as today. For next day's first load version will again be 1.

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 2

Also is there a way to handle UNC path configuration as source folder for picking up files to be loaded ? From my local path, package works wonder.

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 3

Thom A - Tuesday, October 10, 2017 8:32 AM
... and the other is just a File System Task.

I'm curious... can this "File System Task" execute Cmd.Exe?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 4

sqlenthu 89358 - Thursday, October 26, 2017 6:47 AM
Also is there a way to handle UNC path configuration as source folder for picking up files to be loaded ? From my local path, package works wonder.

I'm "bumping" this for you because I'd like to know the answer, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 5

Jeff Moden - Thursday, October 26, 2017 9:10 PM
sqlenthu 89358 - Thursday, October 26, 2017 6:47 AM
Also is there a way to handle UNC path configuration as source folder for picking up files to be loaded ? From my local path, package works wonder.
I'm "bumping" this for you because I'd like to know the answer, as well.

Jeff, it worked. There was some permission issue. However unfortunately using SSIS taking very long time to complete the load work. Say for testing I used around 10 text files with each having size of about 500-800 mb. The number of columns are around 16. VARCHAR(50) is max size with 2 int and 1 numeric field. It took almost 3 hours and it was still running. Will it be faster using any other way ?

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 6

sqlenthu 89358 - Thursday, October 26, 2017 11:30 PM
Jeff Moden - Thursday, October 26, 2017 9:10 PM
sqlenthu 89358 - Thursday, October 26, 2017 6:47 AM
Also is there a way to handle UNC path configuration as source folder for picking up files to be loaded ? From my local path, package works wonder.
I'm "bumping" this for you because I'd like to know the answer, as well.
Jeff, it worked. There was some permission issue. However unfortunately using SSIS taking very long time to complete the load work. Say for testing I used around 10 text files with each having size of about 500-800 mb. The number of columns are around 16. VARCHAR(50) is max size with 2 int and 1 numeric field. It took almost 3 hours and it was still running. Will it be faster using any other way ?

I think I will have to get bulk admin administrator role for my function I'd as well as proxy permission for running xp_cmdshell. Any other idea to get the files loaded faster ? SSIS definitely not working faster (tried and tested).

Paulo de Jesus Default port Points: 1403 More actions · Answer 7

Does your destination table already contain data?
If so, maybe you could import your data into a staging heap table first (empty table with no indexes)

David Burrows SSC Guru Points: 65143 More actions · Answer 8

Jeff Moden - Thursday, October 26, 2017 9:10 PM
sqlenthu 89358 - Thursday, October 26, 2017 6:47 AM
Also is there a way to handle UNC path configuration as source folder for picking up files to be loaded ? From my local path, package works wonder.
I'm "bumping" this for you because I'd like to know the answer, as well.

Is that using UNC to a configuration file or passing UNC to SSIS.
I do not use configuration files but I know you can, in a SQL job, specify UNC for a configuration file or pass UNC as a variable.
I have one package that uses a variable for the import folder to which I pass a UNC

Far away is close at hand in the images of elsewhere.
Anon.

Phil Parkin SSC Guru Points: 246987 More actions · Answer 9

Jeff Moden - Thursday, October 26, 2017 9:09 PM
Thom A - Tuesday, October 10, 2017 8:32 AM
... and the other is just a File System Task.
I'm curious... can this "File System Task" execute Cmd.Exe?

No. The FST is for file and folder operations only. What did you have in mind? There are probably other ways ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246987 More actions · Answer 10

Jeff Moden - Thursday, October 26, 2017 9:10 PM
sqlenthu 89358 - Thursday, October 26, 2017 6:47 AM
Also is there a way to handle UNC path configuration as source folder for picking up files to be loaded ? From my local path, package works wonder.
I'm "bumping" this for you because I'd like to know the answer, as well.

Assuming the package has been deployed according to the 'project deployment model', configuration of source folder paths can be achieved by making the source folder path a 'parameter' in the package and then mapping an SSIS environment variable to it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246987 More actions · Answer 11

sqlenthu 89358 - Thursday, October 26, 2017 11:30 PM
Jeff Moden - Thursday, October 26, 2017 9:10 PM
sqlenthu 89358 - Thursday, October 26, 2017 6:47 AM
Also is there a way to handle UNC path configuration as source folder for picking up files to be loaded ? From my local path, package works wonder.
I'm "bumping" this for you because I'd like to know the answer, as well.
Jeff, it worked. There was some permission issue. However unfortunately using SSIS taking very long time to complete the load work. Say for testing I used around 10 text files with each having size of about 500-800 mb. The number of columns are around 16. VARCHAR(50) is max size with 2 int and 1 numeric field. It took almost 3 hours and it was still running. Will it be faster using any other way ?

Can you post the DDL of the target table? In particular, it will be interesting to know about the PK, indexes and FKs on the target table.
Please pardon the innuendo, but can also you post some screenshots of your package? Include the overall Control Flow + the overall Data Flow + the advanced Component Properties for the data flow destination.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 12

Phil Parkin - Friday, October 27, 2017 5:57 AM
Jeff Moden - Thursday, October 26, 2017 9:09 PM
Thom A - Tuesday, October 10, 2017 8:32 AM
... and the other is just a File System Task.
I'm curious... can this "File System Task" execute Cmd.Exe?
No. The FST is for file and folder operations only. What did you have in mind? There are probably other ways ...

Nothing special in mind there, Phil. It was a question to help me learn more about the various tasks in SSIS and to alleviate a bit of "surface area" fear.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 13

sqlenthu 89358 - Friday, October 27, 2017 2:06 AM
I think I will have to get bulk admin administrator role for my function I'd as well as proxy permission for running xp_cmdshell.

No. Absolutely not. A proxy for running xp_CmdShell is one of the worst security violations possible. Note that I did NOT say that use of xp_CmdShell is the worst security violation possible. The package or system should be able to run xp_CmdShell without making the huge mistake of giving an individual or application login proxy-privs to run it directly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)